Help with Complicated Query

[Guest]

Hi
I would like to create a query whcih dies the following but not sure how to
as cross tab query doesn't allow multiple data.

I would like to have an end result as follows
1st column has all the store details
2nd column has all the + units
3rd column has all the - units
4th column has total of units (add + & - together)
5th column has Cost
6th column has Sell

The 2nd, 3rd & 4th column comes from counts table
The 5th & 6th comes from another query
the 1st column can come from either table

I hope the above is enough information to help me.

thanks
Noemi

 

[Guest]

Post a sample of your data and then how that data would look in the crosstab
query output.

 

[Guest]

Hi Karl
I have posted test data, I hope it makes sense. Thanks Noemi

tbl_Counts
WkCommenceDate StoreNo Keycode StoreCounts MRCounts Adjustment
05-Feb-07 1002 36537075 4 6 -2
05-Feb-07 1004 36537075 4 4 0
05-Feb-07 1005 36537075 12 14 -2
05-Feb-07 1189 36537075 0 3 -3
05-Feb-07 1002 36537082 4 2 2
05-Feb-07 1004 36537082 10 3 7
05-Feb-07 1005 36537082 7 11 -4
05-Feb-07 1150 36537082 4 4 0
05-Feb-07 1189 36537082 0 4 -4
05-Feb-07 1004 36537129 0 1 -1
05-Feb-07 1189 36537129 2 2 0
05-Feb-07 1002 36537570 6 4 2
05-Feb-07 1004 36537570 2 4 -2
05-Feb-07 1005 36537570 0 3 -3
05-Feb-07 1150 36537570 3 4 -1
05-Feb-07 1189 36537570 5 5 0
05-Feb-07 1002 36537587 3 2 1
05-Feb-07 1004 36537587 4 8 -4
05-Feb-07 1005 36537587 1 2 -1
05-Feb-07 1150 36537587 2 2 0
05-Feb-07 1189 36537587 4 5 -1

tbl_Brand
BrandNo BrandName FullName
1 Revlon Revlon
10 Innoxa Innoxa
2 Rimmel Rimmel
3 L'Oreal L'Oreal
4 Maybelline Maybelline
5 Covergirl Covergirl
6 MaxFactor MaxFactor
7 Australis Australis
8 FOA Face Of Australis
9 PhysiciansFormula PhysiciansFormula

tbl_Keycodes
Keycode BrandNo Description Color Cost Sell
36537075 8 LIQUID EYELINER BLACK 2 3
36537082 8 EYE PENCIL BLACKBILLI 2 3
36537129 8 LIQUID EYELINER BROWN 2 3
36537570 8 BLUSH OUTBACK NUTMEG 2 3
36537587 8 BLUSH OUTBACK TERRA 2 3
36537655 8 PRESSED POWDER BEIGE 2 3
36537662 8 PRESSED POWDER BISQUE 2 3
36537679 8 PRESSED POWDER TAN 2 3
36537709 8 BRONZING POWDER BRONZE 2 3
36537716 8 LOOSE POWDER TRANSLUCEN 2 3
36818129 8 PRESSED POWDER IVORY 2 3
36980451 7 PENCIL SHARPENER 2 3
37657789 8 STICK FOUNDATION IVORY 2 3
37657796 8 STICK FOUNDATION BEIGE 2 3
37657819 8 STICK FOUNDATION TAN 2 3
37832810 10 DEF SUPER VITALISER 2 3


End Result

Store No Brand Name Units(+) Units(-) Units Total Total Cost Total Sell
1001 Australis 10 -4 6 12 18
1001 FOA 2 -10 -8 -16 -24
1002 Australis 3 -20 -17 -34 -51
1002 FOA 0 -5 -5 -10 -15

 

[Guest]

Try these three queries ---
Noemi_Counts ---
SELECT tbl_Counts.WkCommenceDate, tbl_Counts.StoreNo, tbl_Keycodes.BrandNo,
Sum(tbl_Counts.StoreCounts) AS SumOfStoreCounts, Sum(tbl_Counts.MRCounts) AS
SumOfMRCounts, Sum(tbl_Counts.Adjustment) AS SumOfAdjustment
FROM tbl_Counts INNER JOIN tbl_Keycodes ON tbl_Counts.Keycode =
tbl_Keycodes.Keycode
GROUP BY tbl_Counts.WkCommenceDate, tbl_Counts.StoreNo, tbl_Keycodes.BrandNo;

Noemi_Keycodes ---
SELECT tbl_Keycodes.BrandNo, Sum(tbl_Keycodes.Cost) AS SumOfCost,
Sum(tbl_Keycodes.Sell) AS SumOfSell
FROM tbl_Keycodes
GROUP BY tbl_Keycodes.BrandNo;

SELECT Noemi_Counts.StoreNo, tbl_Brand.BrandName,
Sum(Noemi_Counts.SumOfStoreCounts) AS [Units(+)],
Sum(Noemi_Counts.SumOfMRCounts) AS [Units(-)],
Sum([SumOfStoreCounts]-[SumOfMRCounts]) AS [Units Total],
Sum(Noemi_Keycodes.SumOfCost) AS [Total Cost], Sum(Noemi_Keycodes.SumOfSell)
AS [Total Sell]
FROM (tbl_Brand INNER JOIN Noemi_Counts ON tbl_Brand.BrandNo =
Noemi_Counts.BrandNo) INNER JOIN Noemi_Keycodes ON tbl_Brand.BrandNo =
Noemi_Keycodes.BrandNo
GROUP BY Noemi_Counts.StoreNo, tbl_Brand.BrandName;