Inventory Project - needed vs. on hand vs. need to order vs on order

[glb466]

Hello. I would consider my self an intermediate Access user, however, I have been tasked with building a "project" database for work and have reached the outer realm of my knowledge. Here is what I am attempting to do....

I work for an ATM company and we receive information from our sales force and schedulers that details all upcoming projects. This is currently being sent to us in very large inefficient spreadsheets. The spreadsheets contain such information as:
Project name
number of installs
number of swaps
number of reprograms
list of parts needed for each location with quantites
date - start and end

I set up the master table and date entry form. This is where I ran into issues. The way I set up the table/form is to have 1 entry page that will capture all relevant information for each project. There are up to 4 different part numbers for ATMs and up to 41 part numbers for all the parts that go with the ATM.

Currently there is the PROJECTS table which is driven by the data entry page. There is a PO table that is driven by a PO entry page. Then there is a GP IMPORT table which is populated by an import from our main inventory system (Great Plains).

I am trying to build a query that will take the information from the PROJECT table and compare it with the PO table and the GP IMPORT table so it will return velues I can put on a report to show our purchasing group what they need to order and the quantity that needs to be ordered. I was able to get this to work when I had a seperate record for each part number. When I put all the PART NUMBER/QTY fields on one page I am not certain how to get Access to do the calculation and combine the results into one line based on part number. One record can have up to 41 seperate parts. Also, if we have more than one project running concurrently then it is possible that for project ABC we have part 123 in the field "PARTNUMBER2" and also part 123 in field "PARTNUMBER30" on the project XYZ. I want Access to look at all fields and then combine them into one line.

I hope this makes enough sense for someone to help and forgive me for rambling. I have attached a screen shot of the project data entry page.

 

[Guus2005]

Did you normalise the database properly?

 

[jdraw]

There are a number of free data models at this site:

http://www.databaseanswers.org/data_models/index.htm

You may wish to review a few and compare to what you have. You mention Project and PO tables, but that doesn't seem to cover the scope of your "proposed system".

I'm not exactly sure what entities/tables you will have, so have not focused on any specific model. Once you review a few, I'm sure you'll find something that is useful.
You do not have to use any particular data model from the list. Your "system" may require parts from several models and/or some custom pieces.

What Guus2005 has suggested is critical. Here is another link with some topics that discuss normalization and Entity Modeling.
http://www.rogersaccesslibrary.com/forum/topic238.html

As for your data entry form - it may be perfect for your situation, but it looks very busy to me. Who will be using the data entry form?

 

[glb466]

Day 2 -- I have rethought the DB design and made some fundamental changes. I have decided to split the main table into 2. One for "project" information and one for the "item". I am trying to use a sub form to provide the data entry on the 'project' form. This seems to work with one major exception. When I add the sub form it either shows the data correctly but will not allow any changes/additions or it will allow changes but not show the correct "project" for the items.

There is currently a table for: project info, all items, system import of current inventory levels, po info, products for projects. The relationships between are based on part # or project name.

Thanks in advance!