Been awhile, tell me if I'm missing something.

[crownedzero]

It's been a long time since I've had anything to do with Access, but I'm back at it again. I am currently using Excel to do a number of inventory reports on a daily basis. I would like to condense these into Access.

There is a daily report of all of our stock in Excel. I would like to be able to import this via a command button making some small updates to the data. (Changing negatives to zero and changing a couple field data types.)

From this data I need to run a query based on our customer criteria, for example Customer1 may only want 10 of our styles while Customer2 may want them all. For the means of this explanation I'll stick to a single customer and replicate it for each additional. I need this range of data in a report that can be exported back to Excel.

I'm going to need my inventory data in a table, "TBL_Inventory", then I'll also need the query "QRY_Customer1". Should this be a normal query or should I be making a table with this query?

Finally, I'm going to need to pull from the queried data along with other data from linked tables. i.e. price, description, etc. all of which optimally would be in individual tables.

Anything I might be missing or suggestions to make this process a little easier?

Thanks in advance.

 

[Galaxiom]

Access can read Excel files. Don't even need to import if you don't have to. From your description it would be better to just link so that Access is connected to the current data. Check out the External Data ribbon (2007) or menu depending on your version of Access.

Results from queries can be directly exported to Excel. No need to make a table. The export parameters can be saved.

Where do all the other tables (price, description) come from? Why would they optimally be in individual tables rather than part of the Inventory table?

Moreover, why bother to cater for your customer's inventory whims? I assume you are providing them with a spreadsheet. Give them the complete file and let them choose the items they want. Would it really matter if they were exposed to the rest of your range. Most sellers would want to show everyone what was available.

If it is essential then I certainly would not be making individual lists unless you have a very small number of customers. Do it for major customers perhaps but at the very least reduce it to a sensible number of groups which customers can be assigned to. Then make a table to record which items are available to each group.

Make a form with a combobox list of customers and a button to run the query and export the results.

Ultimatley you could use VBA to automatically loop through the variants of the query and export them to individual Excel documents. Firstly though, concentrate on linking the data and then the design of the custom inventory list table and associated query.

Get something working then you will be able to get more help here once you identify more specific questions.