Cross Tab Quary

  • Thread starter Thread starter PO
  • Start date Start date
P

PO

OK, I will SOOO HAPPY if anyone can solv this for me:
Access 2007

Tab1: Order Tab. Includes Styles, Colour and Size (dropdown from style,
colour and size tables respectively) and then quantity that I fill in
manually insted of drop down.

Tab2: Packing Tab. same as tab 1.

Now I want to create a quarry with columns, Creating a table (recordset)
with: Styles, Colour and Sizes, plus quantity from tab 1 and quantity from
tab 2.

Note that Tab 1 may contain some variants of Styles, Colours and Sizes that
not yet has been packed, i.e. not in Tab 2. Also the packed list will
normally appear in another sequence than Tab 1, as Tab 1 is created when an
order is given and Tab 2 gets it info as and when things are ready which may
happen in a mixed order.

As I am quite new to this (used to other types of system than Access)
 
Select Styles, Colour, Size, Sum(quantity)
FROM (SELECT Styles, Colour, Size, quantity
From Orders
UNION ALL
SELECT Styles, Colour, Size, quantity
From Packing )
Group By Styles, Colour, Size
Order By Styles, Colour, Size ;

First make sure that the all the field names and table names are correct in
the SQL statement above. Then open up a new query without picking any tables.
Go to SQL view and paste in the statement above once you've fixed it.

Whats going on:
First off we need to combine the two tables using a UNION ALL statement.
That's one reason why putting the same kind of data into multiple tables is
usually a very bad idea.

Once we got the data collected from the two tables in the UNION ALL, we use
it in a Totals query to sum up the quantities for each combination of Styles,
Colour, and Size.

Now depending on how the data is populated from the combo box dropdowns, you
might see numbers instead of Styles or Colours. If so that will take more
work to figure out.
 
OK, thanks Jerry. Have moved me a bit forward but not produced what I wanted.

1. What Jerry told me:
Select Styles, Colour, Size, Sum(quantity)
FROM (SELECT Styles, Colour, Size, quantity
From Orders
UNION ALL
SELECT Styles, Colour, Size, quantity
From Packing )
Group By Styles, Colour, Size
Order By Styles, Colour, Size ;

2. What I did:
Select Styles, Colour, Size, Sum(Quantity)
FROM (SELECT Styles, Colour, Size, Quantity
From QOrder
UNION ALL
SELECT Styles, Colour, Size, Quantity
From QPacking )
Group By Styles, Colour, Size
Order By Styles, Colour, Size ;

3. What SQL View shows after reopening the same:
SELECT [%$##@_Alias].Styles, [%$##@_Alias].Colour, [%$##@_Alias].Size,
Sum([%$##@_Alias].Quantity) AS SumOfQuantity
FROM (SELECT Styles, Colour, Size, Quantity
From QOrder
UNION ALL
SELECT Styles, Colour, Size, Quantity
From QPacking ) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].Styles, [%$##@_Alias].Colour, [%$##@_Alias].Size
ORDER BY [%$##@_Alias].Styles, [%$##@_Alias].Colour, [%$##@_Alias].Size;

4. What is shown in Querry Table -Recordset:
- After RUN it askes for a Enter Parameter Value and specify Styles.
However in the Style column that style number I am giving is shown, however
the other columns remain the same with same info and figures regardless of
style number.
-Then in the table there is ONE quantity shown. That quantity is the
Quantity in Order PLUS quantity in Packing.

5. What I want;
-That the style part functions right.
-That I get 3 columns for quantity.
-One which is the same as Quantity in Order.
-One which is the same as Quantity in Packing
-One that show (Quantity in Order) - (Quantity in Packing)

Is it possible?
 
Back
Top