Gary Williams
03-18-2008, 12:31 PM
Hi All,
I'm trying to get from the Sales table below to Sales Report #2. The application has 4 tables (Client, Product, Colout & Size). All sales are recorded in table: Sales. By using the "Simple Report Code" I can easily produce Sales Report #1. What I actually need is Sales Report #2. Is a nested joins solution needed here? If so, can one of you genius's put me out of my misery or is there any alternative solution? I've attached a pdf of the tables if this helps.
Regards
Gary
(Distressed of Benfleet, UK)
===========================================
Client
clientID client_name
1 John
2 Mary
3 Claire
4 Steven
5 Tony
Product
productID product_type
1 Hammer
2 Spanner
3 Screwdriver
4 Adjustable Wrench
5 Punch
Colour
colourID colour_name
1 Red
2 Blue
3 Green
4 Yellow
5 Black
Size
sizeID size_name
1 Small
2 Medium
3 Large
Sales
salesID clientID productID colourID sizeID
1 1 1 1 1
2 1 2 4 2
3 1 3 2 3
4 2 2 3 1
5 3 3 5 2
6 4 1 5 3
7 4 5 5 1
8 5 2 2 2
9 5 4 1 3
========== SIMPLE REPORT CODE ========================
<TABLE>
<% ' ----------- Search Sales Table ------------------- %>
<% strSQL = " SELECT * FROM Sales" %>
<% ' ----------- Display Sales Report ------------------- %>
<% Set ors = ocn.Execute(strSQL) %>
<% Do while not ors.EOF%>
<TR>
<TD><% = ors("salesID")%></TD>
<TD><% = ors("clientID") %></TD>
<TD><% = ors("productID") %></TD>
<TD><% = ors("colourID") %></TD>
<TD><% = ors("sizeID") %></TD>
</TR>
<% ors.MoveNext%>
<% loop%>
<% ors.close %>
<% set ors = nothing %>
</TABLE>
======================================
Sales Report #1
Sale Number Client Product Colour Size
1 1 1 1 1
2 1 2 4 2
3 1 3 2 3
4 2 2 3 1
5 3 3 5 2
6 4 1 5 3
7 4 5 5 1
8 5 2 2 2
9 5 4 1 3
Sales Report #2
Sale Number Client Product Colour Size
1 John Hammer Red Small
2 John Spanner Yellow Medium
3 John Screwdriver Blue Large
4 Mary Spanner Green Small
5 Claire Screwdriver Black Medium
6 Steven Hammer Black Large
7 Steven Punch Black Small
8 Tony Spanner Blue Medium
9 Tony Adjustable Wrench Red Large
I'm trying to get from the Sales table below to Sales Report #2. The application has 4 tables (Client, Product, Colout & Size). All sales are recorded in table: Sales. By using the "Simple Report Code" I can easily produce Sales Report #1. What I actually need is Sales Report #2. Is a nested joins solution needed here? If so, can one of you genius's put me out of my misery or is there any alternative solution? I've attached a pdf of the tables if this helps.
Regards
Gary
(Distressed of Benfleet, UK)
===========================================
Client
clientID client_name
1 John
2 Mary
3 Claire
4 Steven
5 Tony
Product
productID product_type
1 Hammer
2 Spanner
3 Screwdriver
4 Adjustable Wrench
5 Punch
Colour
colourID colour_name
1 Red
2 Blue
3 Green
4 Yellow
5 Black
Size
sizeID size_name
1 Small
2 Medium
3 Large
Sales
salesID clientID productID colourID sizeID
1 1 1 1 1
2 1 2 4 2
3 1 3 2 3
4 2 2 3 1
5 3 3 5 2
6 4 1 5 3
7 4 5 5 1
8 5 2 2 2
9 5 4 1 3
========== SIMPLE REPORT CODE ========================
<TABLE>
<% ' ----------- Search Sales Table ------------------- %>
<% strSQL = " SELECT * FROM Sales" %>
<% ' ----------- Display Sales Report ------------------- %>
<% Set ors = ocn.Execute(strSQL) %>
<% Do while not ors.EOF%>
<TR>
<TD><% = ors("salesID")%></TD>
<TD><% = ors("clientID") %></TD>
<TD><% = ors("productID") %></TD>
<TD><% = ors("colourID") %></TD>
<TD><% = ors("sizeID") %></TD>
</TR>
<% ors.MoveNext%>
<% loop%>
<% ors.close %>
<% set ors = nothing %>
</TABLE>
======================================
Sales Report #1
Sale Number Client Product Colour Size
1 1 1 1 1
2 1 2 4 2
3 1 3 2 3
4 2 2 3 1
5 3 3 5 2
6 4 1 5 3
7 4 5 5 1
8 5 2 2 2
9 5 4 1 3
Sales Report #2
Sale Number Client Product Colour Size
1 John Hammer Red Small
2 John Spanner Yellow Medium
3 John Screwdriver Blue Large
4 Mary Spanner Green Small
5 Claire Screwdriver Black Medium
6 Steven Hammer Black Large
7 Steven Punch Black Small
8 Tony Spanner Blue Medium
9 Tony Adjustable Wrench Red Large