Multi-level sub forms

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

I have the following SQL tables:

Customers: CustId CustName
Markets: MarketId MarketName

Sales: SaleId,MarketId,CustId, ...... (details) .....

The same customer can have details in more than one market.

How can I display this in a 3 level embedded form with the Market as the top
level then the cutomers and finally the sales.
What I can't figure out is the connection between the top level and the
second (i.e. the market and the customer). They have no common key so how do
I connect them?


--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Jonathan Blitz said:
I have the following SQL tables:

Customers: CustId CustName
Markets: MarketId MarketName

Sales: SaleId,MarketId,CustId, ...... (details) .....

The same customer can have details in more than one market.

How can I display this in a 3 level embedded form with the Market as the top
level then the cutomers and finally the sales.
What I can't figure out is the connection between the top level and the
second (i.e. the market and the customer). They have no common key so how do
I connect them?
Click to expand...

The common key is in Sales, so that should be the second level form.
Requerying a linked Sales subform is automatic, but you may have to force a
requery at the 3rd level.

If you want to see the Sales for a particular customer in a specifc market,
then you need to choose the Market and the Customer from unbound combo or
list boxes to use as search sources. The query underlying the Sales subform
will use the Form criteria from the 2 combo/list boxes.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top