Solved Customers with more than 1 address, select 1 for report

[KoskeKos]

Hi all.
I got to point that cant finish:

I got some customers that have more than 1 address and they are in separate tables -customers and addresses.

1. Customer is selected via combobox.
2. If customer have more than 1 address, pop up form will show all addresses with checkbox to select <<< im stucked here on this step
3. selected address should be printed on report <<< haven't idea how to do this

Dont know how to finish.

 

[Uncle Gizmo]

I got some customers that have more than 1 address and they are in separate tables -

Put the addressee in the same table ...

 

[arnelgp]

what is the report all about? can you save the ID of the selected address to another table?
therefore you only print from this table.

 

[Pat Hartman]

You are missing an important piece of the Customer-Address relationship and that is a field that tells you what an address is for. Two common uses are billing and shipping. When you create an order, and you have multiple customer addresses, you select the shipping address for the order and only the selected address will be printed. You can also define the billing address as the default address and use that one when a different one isn't selected.

 

[Cotswold]

How about a Query with both Address Tables and the Customer Table? Link each Address Table to the Customer using the CustomerCode and sort as required. You will of course show duplicate Customers, if a Customer has two addresses in one table and three in another, then the Customer will show five times.

 

[Cronk]

Put the addressee in the same table ...

Fine if there is say only two addresses (Billing and Shipping) but what about the case where a customer has multiple depots to here orders are to be dispatched.

 

[GPGeorge]

Fine if there is say only two addresses (Billing and Shipping) but what about the case where a customer has multiple depots to here orders are to be dispatched.

Business rules influence proper table design to a large extent. In other words, one size doesn't fit all applies here any better than it does to hats or shoes. That's the problem with templates and other off-the-shelf applications.

If you are designing a relational database application for "Mom & Pop's Corner Candy Store", a single address might be fine.

If you are designing a relational database application for "Adam and Eve's Regional Pet Shops", you might have to support half a dozen locations, each with an address or two.

The way related tables are structured needs to reflect that reality, not an abstract "ideal" normalized database design.

 

[Uncle Gizmo]

but what about the case where a customer has multiple depots to here orders are to be dispatched

One address table will suffice.

You could have a Many to Many table to match customers & addresses, as it is possible that two customers reside at the same address...

 

[KoskeKos]

Thank you all for effort.
Let me present you the real situation:
Company 1 have 5 address.
I need to send 3 invoces on that Company 1.
I must be able to create all three and print them with same company name but differrent addresses.
For learning pupose I have:
t_Customer, t_CustomerAddress, t_Invoice and f_Invoice with comboCustomer.

1. Select customer
2. if there are more address than one: open f_CustomerAddress with checkboxes, store that value into t_Invoice.
3. Print report with that stored address.

If this is ok concept,
how can i store checked address from f_CutomersAddress into t_Invoce?

 

[arnelgp]

you need to save the "billing/delivery" address in your invoice table.
see this demo.

 

[KoskeKos]

you need to save the "billing/delivery" address in your invoice table.
see this demo.

Thank you arnelgp I will check this.

 

[Pat Hartman]

Are you sending the same invoice to all three locations? If so, then just mark the address records as billing addresses. Then your query will join to the address table and use criteria to select only billing addresses. the invoices will be automatically duplicated for as many addresses as are marked as "billing".

If the invoices are unique, then you need to mark the order when it is placed with the billing address to use when it comes time to send the invoice. Then the query would join to that specific invoice record based on its PK rather than joining on companyID and using a flag to duplicate the invoices.

 

[KoskeKos]

you need to save the "billing/delivery" address in your invoice table.
see this demo.

Once again, I would like to thank everyone and especially to arnelgp say that I managed to solve the problem.
Addresses are in separate table so i can have many for one customer.
Long Live Good (Read: access-programmers.co.uk) People!

Customer name < Naziv kupca
Adresa < Address
Odaberi adresu < Choose address.