D
doctorjones_md
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
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
