Multi-level imbedded 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."
 
Hi Jonathan,

You really can't connect two forms which don't have common keys. You can
have a subform which is not linked to the mainform but that doesn't seem
like what you want here.

You want to show a Market record, then show (all or some) Customers, then
show the Sales for the market and customer?

I'm thinking you might want to use an unbound main form, with combo's or
listboxes for selecting the Market and Customer then have a subform which
shows the Sales for the selected market and customer.

Post back with more details about what you are trying to do and I'll be glad
to offer some options.
 
Hi Jonathan,

Sandra posted good suggestions; I'd like to know what the relationship
between Customers table and Markets table? Based on your 3 level modules,
the relationships should be:

Market to Customers: one-to-many
Customer to Sales: one-to-many

If the relationships are not as above, I think 3 level modules are not
possible.

You may want to refer to the database design related articles.

283878 ACC2002: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=KB;EN-US;283878

283698 ACC2002: "Understanding Relational Database Design" Document Ava
http://support.microsoft.com/default.aspx?scid=KB;EN-US;283698

289533 ACC2002: Where to Find Information About Designing DB in Access
http://support.microsoft.com/default.aspx?scid=KB;EN-US;289533


Please feel free to reply to the threads if you have any questions or
concerns.


Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.



--------------------
| From: "Jonathan Blitz" <[email protected]>
| Subject: Multi-level imbedded forms
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| 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."
|
|
|
 
Back
Top