Would this be an Access solution or something else? 4

[anationalacrobat]

The dreaded nightmare we always hear about is "oh, well this is something simple we can do on a spreadhseet" but invariably more and more layers of complexity will be added until it's a hacked together nightmare to work with. I'd like to be pointed in the right direciton on how to approach this.

For now, the company has a list of people that have to be tracked with certain stock information required for internal reporting and later mailing of individualized letters. The main CRM system cannot handle the details and so the initial plan was just to store the names and information in a spreadsheet and be done with it.

Well, I'm getting visions of the nightmare scenario I outlined above and I can see it starting now. For starters, there's more than one line of information per person now. This makes the mail merge a royal pain in the butt because you're stuck using word field merge codes to loop multiple records per person and it's all stupid.

My initial reaction is that this looks like a simple Access app. Table for people, table for the stock info, linking table tying the two sets together, and the letters can be generated from a form.

Now here's the problem. The coworker with the request doesn't want to use Access because her experience has been that few people know how to use it and she's been stuck with stuff she can't maintain when the guru moves on to greener pastures. Also, she thinks that InfoPath might be able to do this sort of thing in a cleaner fashion.

So I ask the gurus here, knowing how feature creep will affect things that should be simple and how it's better to pick the right tool for current AND future needs rather than just current needs, which tree should I be barking up here, Access, InfoPath, or something else?

Thanks!

 

[SkipVought]

Hi,

Not without knowing more specifics.

Excel has only 65,536 rows. Is that a problem?

Why are there multiple rows per person?

How do you NOW get from that multiple to a single record MailMerge?

An example or two might help.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[fneily]

Don't know anything about InfoPath.
But if what you presented is ALL there is, then in Access you'd have, as you already suggested, three tables: tblCustomer, tblStockInfo, tblCustStock. tblCustStock is called a junction table. You have a many-to-many relationship -one customer can have many stocks, one stock belongs to many people. Relational databases cannot handle many-to-many relationships so you build the junction table. In this table would be the primary keys of tblCustomer and tblStock, CustID and StockID, and any COMMON fields such as Quantity, DatePurchased, DateSold, etc. (Notice, NOT price -that is a calculated field which you don't store in databases). Then you can do a mail merge by setting up a simple report. If you get that far, I can explain how to do it.
A spreadsheet (Excel) has duplicate data such as customer name, stock name, etc. You would not have these duplicates in an Access table. If you normalize your tables, that is. That's the purpose of Access. And, if the tables are set up correctly, future expansion is not a problem. Since you stated that in your Excel, a person's info spans more then one row, I know you have more then 255 columns of data on that single person. Right there I know something is not being done right. That's alot of demographics.
As far as the opinion of your coworker - in my old age, above 55, I have grown less tolerant of people who want to stay ignorant and uneducated. Notice she said that InfoPath may be better to use and yet has no knowledge of Access. I'm sure the color of her shoes are more important.

 

[MMSMSD]

I've not used InfoPath, so I can't comment on that program. I would agree that setting it all up in Access seems like the best option. One alternative though would be to use both Excel and Access. While not quite as clean as a full Access database, it might work for you and give your coworker a level of comfort.

Create an Excel Workbook, with separate spreadsheets for each 'Table' of data. Then link to that workbook in the table section of Access and do all your correlating, data entry screens and queries. Finally, if you so choose, use Word to create your letters and merge the Access queries into them.

Should anything happen with the Access portion (or the person who put it together), your coworker can get to the raw data within Excel and work as she normally would to get the data out to the letters. Of course, after a few times of her trying to bypass Access, she'll hopefully decide that keeping it all in Access is best. At that point, your data is already formatted as needed and can be imported into true tables. Hope this helps.

 

[anationalacrobat]

Sure thing!

Ok, here's what a sample record looks like in Excel now

name address stock name qty price
bob 123 place abc corp 10 20
bob 123 place def corp 5 30
sue 334 circle abc corp 10 15

(additional fields omitted for clarity. qty*price is stored as a field and the total value of all the customer's stocks are stored as a value at the end of each record. So bob here has $350 stored at the end.)

Obviously, that's a recipe for disaster. I've already demonstrated to the person making the request that I can't group this stuff accurately because of typos and other insanity.

Now, how does Word do the merge? Oh, this will make your teeth hurt. There are two columns that record the line item number and total number of line items: they are pyseq and tpaycnt (pay sequence and total pay count). The Word field codes are doing a loop in the mail merge.

Since Word won't let me cut and paste the field codes, this is just a simplified representation of what's going on.

{if { mergefield pyseq} =1 "

/*body of letter goes here*/

date: stock name: paymetn amount:
/*loop begins here */
{mergefield pydate}{mergefield stock name}{tpaycnt}
{if {mergefield pyseq} = {mergefield tpaycnt}"
total pay is: {mergefield totalpay}

Not quoted above are the "advance" field codes which allow for positioning of text within the merge area. The merge will run right now with funky positioning errors; if I try to correct the positioning, Word crashes.

In other words, this is a very stupid and buggy way of trying to get the job done. This is a hack of a hack that was adapted from another hack used to generate letters from the main management system.

What I'd like to find out is the "best practices" way of getting the job done and then adapt it to our specific situation. As I said, I'm assuming this calls for Access but there might be a smarter way to do it these days.

 

[SkipVought]

If you're ONLY wanting to get the Name Address portion, you could use Data > ADvanced Filter to generate a unique list.

I'd prefer to use MS Query faq68-5829.

But in the long run, you need a better solution, as fneily has suggested.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[anationalacrobat]

Well, I think using Excel for any of this is screwed up to begin with. The biggest problem with doing the "Access calls Excel sheets" part is that it still doesn't remove the possibility of stupid data entry errors. That's why I was leaning towards Access in the first place. So now the question is what Microsoft's web forms support looks like these days. Back when I last used Access for something like this, everyone who was going to access the application needed their own copy of Access on their desktop, or you could manually code ASP pages to access the database. I'm sure there's a less labor intensive way to do it now. (I hope!)

 

[genomon]

The coworker with the request doesn't want to use Access because her experience has been that few people know how to use it

A couple things:
1) You don't need to know how to "use Access" to operate a well-designed form.
2) Even the small operation I work in has mandatory semi-annual skills refreshers and training; if coworkers are that paranoid about having a new icon on their desktops, maybe you can set something up with your training or HR depts., or throw something together yourself?

All I ask is a chance to prove that money can't make me happy.

 

[anationalacrobat]

A couple things:
1) You don't need to know how to "use Access" to operate a well-designed form.

Me: Since when does logic have anything to do with coworker requirements? I agree with you on that but a measure of evangelism will be required to bring people onboard.

2) Even the small operation I work in has mandatory semi-annual skills refreshers and training; if coworkers are that paranoid about having a new icon on their desktops, maybe you can set something up with your training or HR depts., or throw something together yourself?

Me: That would be the path I would take on this. IT is looking at a pile of different technologies to try to automate parts of the business process. I'm dilligently going along with the research but am concerned that we could end up with a beast that's becomes more work than its worth. The IT boss is looking into Sharepoint this and that, we have InfoPath as a part of the basic workstation install, etc.

Me: What makes me so skeptical is that I've come from organizations with very poor compliance with procedures in general, not even bringing computers into the equation. I'm at a new company now and so am still trying to get a feel for the corporate culture and way of doing things. Historically, at previous companies, I've seen disorganization starting at the top and chaos trickling down. It doesn't matter how good of a tool I put together if it will never be used. My approach is to try and keep things as simple as possible, only throwing as much tech at a problem as is required to get it done, and provide gentle, patient training.

Me: So to make a long story short, looks like I'm going to be learning up on some Access!

 

[fneily]

Again, looking at your sample data, you'll notice duplicate names, addresses and stock names. All avoided in Access. They would only be typed in once and in only one place. You're storing the qty*price value. Not allowed in Access - not necessary.
Setting up a mail merge with an Access report is just changing three report properties - ten seconds and you're done.
You later mentioned the web. You can create DAP's (Data Access Pages). It's a different animal, but if you just need it for simple data entry, then it isn't so bad. If you search Microsoft: Access Other topics on DAP or "data access page", you'll see I answered quite a few questions on them.
You mentioned Sharepoint. Depending on the version of Access, you can use DAP's or connect directly to the Access database using Sharepoint lists.
Your co-workers will only be seeing a data entry form. So you make it in Access or an Excel form. Put some flowers on it and they'll be happy.

 

[xlbo]

Definitely one for access - anything where tracking needs to happen is better performed in a database

Unlike other respondants, I HAVE used Infopath......and its a pig !!!! It is data entry only for the most part and in reality it is a "front end" into a database. It cannot store data itself

It is the equivalent of building a form in access but harder to do (IMHO) - generally used for entering data into and reading data from databases but kinda pointless for Access as it has native functionality to do this (forms / reports etc)

All this user would be doing is transferring a lack of Access knowledge to a lack of Infopath knowledge!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[anationalacrobat]

Xlbo, that's precisely the kind of answer I was looking for regarding InfoPath. The problem I have with the newer Microsoft products is that there's so much difficulty in cutting through the marketspeak to figure out what the fool things even do! Proactively leverage my synergies my left foot!

This gets back to the old planning question of "Do you have a need for which product x is the best solution or do you have product x and are trying to find something to use it for?" Then there's the other problem of "Is product x on paper the best fit for your needs but it's too buggy to rely on in practice?" I've seen that problem before, the solution works well enough to pass the proof of concept phase but can never be polished enough to meet deployment requirements.

 

[xlbo]

no worries - the only thing I would say is that Infopath can be embedded into Sharepoint and it can look pretty seamless but you could just as easily provide a hyperlink to an access database on the LAN that pops up a form by default so users don't need to "see" that it is access they are using....

Can certainly sympathise with your position - have been in the "ooooh we've got a new product - what can we use it for situation" before myself

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question