create report from joined tables

[eviephillips]

I have the same problem!! I have joined 4 tables. All records are pulling from the database. That is good. However, when a shirt is in 2 colors and 10 sizes, each color repeats 10 times in the dynamic drop down box, and the 10 sizes repeat twice.

I know there is an answer to this, because other e-commerce sites display properly in their size and color drop down box.

Will someone please tell me the answer in English!!!

Evie

[Split from another thread - Please ask questions in your own thread (Admin)]

[Rabbit]

You're doing a report so I don't know why you would want to use a combo box. Not aesthetically pleasing.

[NeoPa]

Have you considered the situation you're asking about?

When linking multiple recordsources (tables etc) together, you should be aware that sometimes the only way to show the information requested is to repeat certain items.

[[U]TableA[/U]]
Item; String
Size; Numeric
[U]Records[/U]
'Shirt'; 8
'Shirt'; 10

[[U]TableB[/U]]
Item; String
Colour; String
[U]Records[/U]
'Shirt'; 'Blue'
'Shirt'; 'Red'
'Shirt'; 'Green'

With a query where the two tables have an INNER JOIN on the Item, there will be a result set of SIX records as each of two sizes match with all three colours.
[CODE=SQL]SELECT TableA.Item, TableA.Size, TableB.Colour
FROM TableA INNER JOIN TableB
ON TableA.Item = TableB.Item
Result Set
Shirt, 8, Blue
Shirt, 8, Red
Shirt, 8, Green
Shirt, 10, Blue
Shirt, 10, Red
Shirt, 10, Green[/CODE]
There's really no other logical way that it could work.