printing results of 2 queries

[Eyal]

I have a table of "stock items "(Style Number, Colours & 5 different sizes).
I have another table of "orders on hand" with (Style Number, Colours & 5
different sizes).
I have two different queries to sort the results of the two tables.
I want to create a report that will display the result of the one table
(stock) less the result of the other table (Orders on hand) in the same
breakdown as the tables (Style Number, Colours & 5 different sizes).
Any suggestions
Many thanks
Eyal

 

[Douglas J. Steele]

If what you want is difference between the two tables, create a query that
does that calculation for you, and use the query as the RecordSource for the
report. However, your tables don't look as though they're properly
normalized: having fields represents "5 different sizes" in a single row is
usually indicative of having a repeating group. You might benefit from
reading some of the resouces Jeff Conrad points to at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Jeff Boyce]

Eyal

Already responded to in another of your posts. Please don't post the same
question to multiple groups independently.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Eyal]

Hi Douglas.
Both tables consist of the following 7 columns 1.Style number(numeric)
2.Colour(Text)
3.Small(numeric)4.Medium(Numeric)5.Large(Numeric)6.XLarge(Numeric)7.XXLarge(Numeric).
I tried subtracting one table from the other in a query but the results are
totally incorrect.

 

[John Spencer]

Assumption: You only have one record in each table for each combination of
Style and Colour

SELECT S.[Style Number], S.[Colours]
, S.Small - Nz(O.Small,0) as SmallCount
, S.Medium - Nz(O.Medium,0) as MediumCount
, S.Large- Nz(O.Large,0) as LargeCount
, S.xLarge- Nz(O.xLarge,0) as xLargeCount
, S.xxLarge- Nz(O.xxLarge,0) as xxLargeCount
FROM [Stock Items] as S LEFT JOIN [Orders on Hand] as O
ON S.[Style Number] = O.[Style Number]
AND S.[Colours] = O.Colours

IF you have MULTIPLE records per Style and Colour you will need to build a
query for each table to sum the records in each table and then a query using
those two queries that looks like the above.

As noted elsewhere, your table design is wrong and makes things more difficult
to do.

A more correct design would be
StyleNumber
Colour
Size (New field stores: Small, Medium, Large, XX, XXX, XXXX, Petite, etc)
ItemCount (The number you now store in one of the five size fields)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County