Help! Newbie to Access under pressure

[ritch]

I work for a Supermarket (corporate) - we have 10 stores - we take a backroom and salesfloor inventory once a week, I also get a master file updated once a week from our vendor (11000 items). I need a way to take the 10 files from the stores and merge them to the master file so I can produce a report showing inventory from each store. The initial files from the stores is a txt doc and from the vendor it is a csv file. I figure that the UPC of the item would be the primary key - but - if a store has 10 units on the shelf, 100 on a display and 20 in the backroom there would be 3 entries for this item in the file - is there a way to make them add the quantities so there would be only 1 entry. HELP!



or maybe just a general direction......

[NTC]

do an Aggregate Query using the Group On for the product and Sum for the quantity...

Aggregate Query is the epsilon icon greek E in the tool bar when in query design mode

[ritch]

any info on how to join or merge the 10 files?

[jbarrum]

If all of your files have the same identical collums then I think your best bet is to create a table duplicating the fields and them import the files into that table. you will find the import utility under the "file" menue in Access. Under the file menue click on "get external data" and choose import.

Link would be a good option if all of your data in excell is in one file.

[ritch]

Excel would be easy for exact files, these files will be similar but no 2 files will be the same

[jbarrum]

will they at least all be in the same format?

[ritch]

That is kinda what I am trying to figure out now.....I know Excel - but I have long heard that one's value increases if you can handle access....
with the files being different I think access would be a better choice but more of a pain to learn - especialy since the files will change weekly. I think that access would handle the accumulation of data better - it would hold the database and I could just filter out what I wanted. In store files I just have Retail Value - in the Master file I have cost and retail and (I think) would be able to create reports with cost, retail - price reductions and the loss related to those reductions.....something the VP would hate me for (so what he already does - he doesn't like computers or IT people)
But the owner does......

[jbarrum]

no matter what you are using you will need to find some way to get all of this data to come together in the same format. Another option might be to use Acess to Link to tables in the sytem that is storing the information. This typically works well but you will need to install the ODBC driver for the store software that is being used. Are all of the stores at least using the same system?

[carlmdobbs]

If you are not an MS Access programmer, you need one. I will gladly talk to you over the phone for an hour or so free. But a novice can't do what you are asking. The best I can do is give you advice and send you a beginning lesson I have written out that shows you how to crete tables and a little about data modeling. Study it and you might find yourself better off than you are now. Just ask for it. carlmdobbs@gmail.com

I work for a Supermarket (corporate) - we have 10 stores - we take a backroom and salesfloor inventory once a week, I also get a master file updated once a week from our vendor (11000 items). I need a way to take the 10 files from the stores and merge them to the master file so I can produce a report showing inventory from each store. The initial files from the stores is a txt doc and from the vendor it is a csv file. I figure that the UPC of the item would be the primary key - but - if a store has 10 units on the shelf, 100 on a display and 20 in the backroom there would be 3 entries for this item in the file - is there a way to make them add the quantities so there would be only 1 entry. HELP!

or maybe just a general direction......