need some help with query

[drive105dj]

I have the following two queries (and I made tables for them as well)

Table A


Item, Lot, Qty in Location A, Qty in Loc B, B's Cost, A's Cost

Table B
Item, Lot, Qty in Loc C

There are items in both tables that aren't in the other table but most items match. How do I make sure that all items and lots are accounted for in one report and that the report turns out like the following?
Qty in A Qty in B Qty in C
Item abcd
Lot 123 22 0 300
Lot 456 41 16 42
Item efgh
Lot 128 7 7 7
Item hijk
Lot 2 21 22 21

[June7]

Why do you have two tables and two queries?

Use report Grouping & Sorting features.

[drive105dj]

Why do you have two tables and two queries?

Use report Grouping & Sorting features.

I have a table version made of each query in the event my help from this site requires either a table or a query. I can use either the tables or the queries. I just need to mash them together so all items and lots show along with corresponding quantities for each loc.

[June7]

Still don't understand why there are two of each as opposed to 1 of each.

Need to join the 2 whichever to a 'master' dataset of Items.

[drive105dj]

Still don't understand why there are two of each as opposed to 1 of each.

Need to join the 2 whichever to a 'master' dataset of Items.

The 2 queries come from two different companies. There is one with loc a and loc b. There is another with loc c. I made a table from each query in the event I needed them. I need to join them to a master, but when I join on item or lot, I get multiple lines for same data.

I am using design view.

My completed project should be item (column a) and lot (column B) down the side with loc a (column c), b (column D), and c (column e) in different columns.

[June7]

Really should be one table with another field for company ID. Multiple similar name fields is not a normalized data structure.

Yes, there will be multiple lines for same data if each table has multiple records for the same item and/or lot.

Is the combination of item and lot unique in each table? Do you have a master dataset of all item and lot combinations? If not, dataset can be created with a query that includes both tables without join and pulls only the item and lot fields (a Cartesian relationship). Then join the two whichever to the master by compound link on item and lot.