I am trying to get a database in Microsoft Access 97 set up to handle the taking of a physical inventory where I work. Everything is pretty well set-up, except I can't get the level of reports out that I will need.
Before we begin the inventory, we will take a snapshot of our system, and load it into an Access table via Excel. This table will show (among other things), part numbers and quantities associated with those parts.
Then we will be taking inventory and entering part numbers and quantity from our physical counts into another table via a form directly in Access.
Now we have the problem, I can combine those two tables without a problem, but the report out the other side needs to show one line per part number, with a column for the snapshot quantity and another column for our counted quantity. I can get a query that looks like that, but it's on two different rows.
What I can get now is:
Part No.--System----Counted
Part A ------10
Part A---------------------10
Part B-------15
Part B---------------------13
Part C--------8
Part C----------------------5
But what I really need it to be is:
Part No.--System----Counted
Part A-------10-----------10
Part B-------15-----------13
Part C--------8-------------5
So that I can subtract the System Quantity from the Counted Quantity. Can someone please help me out and show me how to get all Part Nos. consolidated even though one Part A comes from one table with one set of data while the other comes from another, especially considering that sometimes it will appear like this:
Part No.--System----Counted
Part A------10
Part B---------------------13
Part C-------8--------------5
Where one part exists only on one table, and another exists only on the other. It makes things really tricky I've found. Since Monday, I've spent all week at work (close to 40 hours) getting this thing set up, and now I am stuck at the very end, and it need to be done by Monday morning! Any suggestions would be great.
Before we begin the inventory, we will take a snapshot of our system, and load it into an Access table via Excel. This table will show (among other things), part numbers and quantities associated with those parts.
Then we will be taking inventory and entering part numbers and quantity from our physical counts into another table via a form directly in Access.
Now we have the problem, I can combine those two tables without a problem, but the report out the other side needs to show one line per part number, with a column for the snapshot quantity and another column for our counted quantity. I can get a query that looks like that, but it's on two different rows.
What I can get now is:
Part No.--System----Counted
Part A ------10
Part A---------------------10
Part B-------15
Part B---------------------13
Part C--------8
Part C----------------------5
But what I really need it to be is:
Part No.--System----Counted
Part A-------10-----------10
Part B-------15-----------13
Part C--------8-------------5
So that I can subtract the System Quantity from the Counted Quantity. Can someone please help me out and show me how to get all Part Nos. consolidated even though one Part A comes from one table with one set of data while the other comes from another, especially considering that sometimes it will appear like this:
Part No.--System----Counted
Part A------10
Part B---------------------13
Part C-------8--------------5
Where one part exists only on one table, and another exists only on the other. It makes things really tricky I've found. Since Monday, I've spent all week at work (close to 40 hours) getting this thing set up, and now I am stuck at the very end, and it need to be done by Monday morning! Any suggestions would be great.