Difficult One-to-Many Query

I have (2) back-end SQL Server tables that I've linked to a front-end ACCESS
db. One table contains Overall (summarized) data associated with a Product
Order, and the other contains Specific data associated with the order. Both
tables have Customer Number as a Primary Key -- here's the structures of the
(2) tables, as well as what I'm trying to do with the data:

Table 1 Overall Data.

(No Primary Key)
FIELDS:
IndexNumber
CustomerNumber
OrderStatus
OrderDate
CompanyName
SalesRep
TotalSaleRevenue

Table 2 Specific Data.

(No Primary Key)
FIELDS:
IndexNumber
CustomerNumber
OrderStatus
OrderDate
ProductName
ProductDescription
ProductPrice

There are (29) possible Products that a customer can select for each
order -- the Sales Rep processes the Order via an EXCEL workbook, and after
configuring the order, rolls the data up to SQL Server via a sSQL = "INSERT
INTO statement -- this process works fine. At some point, we'll want to do
some analysis on Quarterly Sales, so I've linked the tables to a front-end
ACCESS db for Reports. Here's where the problem lies ....

There's a 1:29 ratio (one-to-many) relationship that needs to be established
between the (2) tables -- I've created a query of both tables, and added
Left-Join from the Specific Data to the Overall Data -- this gives me all
data from both tables. When I display this combined table on an ACCESS
form, I need to be able to show all (29) possible Products that were ordered
for each CustomerNumber -- since each Product ordered is in a seperate line
(recordset), I'm not certain how to achieve this (getting all (29) on a form
when I search for a particular CustomerNumber.

Many thanks in advance for any assistance on this one.

Shane

On Fri, 20 Apr 2007 12:56:35 -0500, "doctorjones_md"
wrote:

I'm not certain how to achieve this (getting all (29) on a form
when I search for a particular CustomerNumber.

Simplest would be to use a Form for the "one" side table with a continuous
Subform for the "many", linked by CustomerNumber.

John W. Vinson [MVP]

Thanks for the suggestion John -- I'll give it a shot!
"John W. Vinson" wrote in message
...
On Fri, 20 Apr 2007 12:56:35 -0500, "doctorjones_md"
wrote:

I'm not certain how to achieve this (getting all (29) on a form
when I search for a particular CustomerNumber.

Simplest would be to use a Form for the "one" side table with a continuous
Subform for the "many", linked by CustomerNumber.

John W. Vinson [MVP]

je voudrais mettre à jour ma boite d'envoi mais je n'arrive pas
pourriez-vous m'aider
"doctorjones_md" a écrit dans le
message de ...
I have (2) back-end SQL Server tables that I've linked to a front-end
ACCESS db. One table contains Overall (summarized) data associated with a
Product Order, and the other contains Specific data associated with the
order. Both tables have Customer Number as a Primary Key -- here's the
structures of the (2) tables, as well as what I'm trying to do with the
data:

Table 1 Overall Data.

(No Primary Key)
FIELDS:
IndexNumber
CustomerNumber
OrderStatus
OrderDate
CompanyName
SalesRep
TotalSaleRevenue

Table 2 Specific Data.

(No Primary Key)
FIELDS:
IndexNumber
CustomerNumber
OrderStatus
OrderDate
ProductName
ProductDescription
ProductPrice

There are (29) possible Products that a customer can select for each
order -- the Sales Rep processes the Order via an EXCEL workbook, and
after configuring the order, rolls the data up to SQL Server via a sSQL =
"INSERT INTO statement -- this process works fine. At some point, we'll
want to do some analysis on Quarterly Sales, so I've linked the tables to
a front-end ACCESS db for Reports. Here's where the problem lies ....

There's a 1:29 ratio (one-to-many) relationship that needs to be
established between the (2) tables -- I've created a query of both tables,
and added Left-Join from the Specific Data to the Overall Data -- this
gives me all data from both tables. When I display this combined table on
an ACCESS form, I need to be able to show all (29) possible Products that
were ordered for each CustomerNumber -- since each Product ordered is in a
seperate line (recordset), I'm not certain how to achieve this (getting
all (29) on a form when I search for a particular CustomerNumber.

Many thanks in advance for any assistance on this one.

Shane