[Fab]

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.

 

[Pseudocyber]

I don't see why you don't have one table. The part number would be the index for the table (unique). A field in the record could be physical count. Another field could be snapshot count. On your report, you print the three fields out and do a text box field on the report, or form, which subtracts the two to give you variance.

HTH

 

[Fab]

The problem is part no. is not unique. We're entering the physical count off of tags. We may have one part no. in three places, requiring three tags, and three records on the count table. But the snapshot table has unique part nos.

So in that case, I have one line in the snapshot table:

Part A-----10 pcs

Yet I have three lines in the count table:

Part A------------------2 pcs
Part A------------------5 pcs
Part A------------------4 pcs

What I need is a report that tells me

Part A------10 pcs-----11 pcs

What I'm getting is

Part A-----10 pcs
Part A------------------2 pcs
Part A------------------5 pcs
Part A------------------4 pcs

Which doesn't help much at all. I need to combine all four of those lines into one line.

I guess the real question is there a way to include in a query all the records of one table AND records in another table that match criteria?

Let's say my snapshot has

Part A-------1
Part B-------1
Part C-------1

And my physical count comes up with

Part B-------------1
Part C-------------1
Part D-------------1

I need to have a report that gives me:

Part A-------1-----0
Part B-------1-----1
Part C-------1-----1
Part D-------0-----1

See my problem? I have a snapshot table with A,B,C. I have the count table with B,C,D. If I do a query trying to link the two I only get B and C.

Snapshot------New Query-------Count
A-(no match)---->
B------------------->B<-----------------B
C------------------->C<----------------C
```````````````<--(no match)-D


I made an append query that includes all of them, but that gives me A,B,B,C,C,D.

Snapshot--------Total--------Count
A------------------->A
B------------------->B
``````````````B<------------B
C------------------->C
``````````````C<------------C
``````````````D<------------D

If I combine the total table with the snapshot, I get A,B,C. If I combine the total table with the count I get B,C,D. I need to combine ALL the records from the total table (ABBCCD) AND all the records from the count table (ABC) OR all the snapshot table (BCD) that match the total table.

What I need is

Snapshot--------Total--------Count
A------------------->A
B------------------->B<------------B
C------------------->C<------------C
``````````````D<------------D

Is that possible? I'm sure it is, but I don't know how.

 

[Fab]

Well, I finally figured it out. I ran a make-table query that made a copy of the first table (ABC) then ran an unmatched query on the second table that returned the result D. Then I appended those results to the make-table, giving me ABCD and only one entry for each part no. It worked fine, once I finally figured it out.