Need Mailing and Billing Address Displayed from Same Table

[Wayne]

Simplified design is:

tblCustomer (1 to M) tblBilling (1 to M) tblBillingDetails

tblBilling (1 to M) tblAddresses (contains Mailing and Billing
addresses)

The tblAddresses table contains an AddressType (Mailing or Billing)
field along with other address fields.

The reason the tblAddresses is linked to the tblBilling address is
because the user needs to see the Mailing and Billing addresses used
at the time of billing. In other words they must see the old addresses
as they view various billing records.

Forms: frmCustomer with frmBilling (subform with 2 tabs) then
frmBillingDeatails subform 2

Many Mailing and Billing Addresses are the same so one address table
makes sense but I would like to display both addresses (Mailing and
Billing) at the same time to the user on one of the Billing forms tabs
but I can't get it to work.
I tried putting two different forms (Mailing and Billing) and on one
of the frmBilling tabs. Each form using the same query to access the
tblAddresses table but that doesn't work.

Is there a way to display both addresses at the same time from the
same table?

 

[Wayne]

Simplified design is:

tblCustomer (1 to M) tblBilling  (1 to M) tblBillingDetails

tblBilling (1 to M) tblAddresses (contains Mailing and Billing
addresses)

The tblAddresses table contains an AddressType (Mailing or Billing)
field along with other address fields.

The reason the tblAddresses is linked to the tblBilling address is
because the user needs to see the Mailing and Billing addresses used
at the time of billing. In other words they must see the old addresses
as they view various billing records.

Forms:   frmCustomer with frmBilling (subform with 2 tabs)  then
frmBillingDeatails subform 2

Many Mailing and Billing Addresses are the same so one address table
makes sense but I would like to display both addresses (Mailing and
Billing) at the same time to the user on one of the Billing forms tabs
but I can't get it to work.
I tried putting two different forms (Mailing and Billing) and on one
of the frmBilling tabs. Each form using the same query to access the
tblAddresses table but that doesn't work.

Is there a way to display both addresses at the same time from the
same table?

I did a little more research and found out that 90% of the mailing and
billing accounts I deal with are different so I'm just going to make
two address tables. Sometimes you just have to get back to simple and
easy.

 

[ManningFan]

I did a little more research and found out that 90% of the mailing and
billing accounts I deal with are different so I'm just going to make
two address tables.  Sometimes you just have to get back to simple and
easy.- Hide quoted text -

You may want to think about using a continuous subform instead. You
can query the same data from the same table and get all the results
that way. If you use 2 different tables to store the same info, the
day WILL come where you regret setting your DB up that way.

 

[Wayne]

You may want to think about using a continuous subform instead.  You
can query the same data from the same table and get all the results
that way.  If you use 2 different tables to store the same info, the
day WILL come where you regret setting your DB up that way.

I think you're right. I'm already running into problems but how can I
display the Mailing and Billing addresses at the same time with a
single continuous form? Maybe I should drop the "display both
addresses at the same time" idea. Maybe a Mailing and Billing button
so the user can switch between the two. Also add a "check if same
address" checkbox and a Current or Old combobox so I can query current
Mailing and Billing addresses. Is this approach better?

 

[Wayne]

I think you're right.  I'm already running into problems but how can I
display the Mailing and Billing addresses at the same time with a
single continuous form?  Maybe I should drop the "display both
addresses at the same time" idea.  Maybe a Mailing and Billing button
so the user can switch between the two.  Also add a "check if same
address" checkbox and a Current or Old combobox so I can query current
Mailing and Billing addresses. Is this approach better?

I can display/edit/add the Mailing "then" the Billing on a single
subform, switching back and forth with buttons, but I do want to
display both addresses side-by-side on a report and haven't figured
out how to do that. The only way I see to do this is to combine the
Mailing and Billing into one table side-by-side but that doubles the
number of fields. Is there a better way?