A case of Linking

[TorrediPisa]

Hello people.
Suppose you have a Clients Table
ID Name
01 Smith
02 Johnson
03 Brown

And two Tables: Orders1 and Orders2 like:
ORDERS1
ID ID_Client Description
01 01 Beer
02 01 Wine
03 02 Whisky
04 03 Brandy
ORDERS2
ID ID_Client Description
01 02 Beer
02 02 Lemonade
03 03 Whisky
04 03 CocaCola

I would like to obtain this report:
ID Client Orders1 Orders2
01 Beer
01 Wine
02 Whisky Beer
02 Lemonade
03 Brandy Whisky
03 CocaCola

If I link the Clients Table to both the Orders1 & Orders2 Tables I obtain a cartesian report.
Is there a way to do that in SQL?
Thanks for yr help.
Regards
TdP

 

[dhookom]

THis is a rather odd request. You could do this by creating a union query of the two tables with a column generated that created a ranking of each liquid within the client
[tt]
OrderNum Rank ID ID_Client Description
1 1 01 01 Beer
1 2 02 01 Wine
1 1 03 02 Whisky
1 1 04 03 Brandy
[/tt]
Use the union query as the basis for a crosstab query that uses ID_Client and Rank as Row Headings, OrderNum as the Column Heading, and First Of Description as the Value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TorrediPisa]

Thank You dhookdom.
I am not so sure to understand. Could you please tell me
how this query shall be done?
Thank You
TdP

 

[dhookom]

Before I go through all the time and effort of creating tables, entering records, and then creating the queries, could you tell me why you think you need this?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TorrediPisa]

Pls do not waste yr time.
Simply pls explain me how to make the ranking column.
Thks

 

[dhookom]

Create a ranking column using the expression
Rank: DCount("ID","ORDERS1","ID_Client ='" & ID_Client & "' AND ID<='" & ID & "'")
This expression assumes the ID and ID_client fields are text since there are leading 0s.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]