Customers Invoice and Collection Address?

hullstorage

Registered User.
Local time
Today, 15:12
Joined
Jul 18, 2007
Messages
213
Hi All,

I am creating a new project as my current database has a lot of flaws?

What I want to know is, our customers can have a Collection Address that differs from their Invoice Address so should I create 2 tables and link them.

Any help thanks

Simon

(Collection Point)
Joe Bloggs Group
Unit 1
Hessle Rd
Hull
HU4 6PL

(Invoice Address)
Joe Bloggs Group
Accounts Payable
Old Road
London
SW4 1AU
 
I'd just have the one table and another field to say Address Type, and a choice of collection or invoice. Saves hassle when reporting then....
 
I'd just have the one table and another field to say Address Type, and a choice of collection or invoice. Saves hassle when reporting then....


But they will have both, so when we enter new account details we would normally enter them both incase they differ.

so basically there will be:

invoice address
collection address ( i.e. their warehouse or one of their customers )
delivery address ( same as above )
 
That's OK - you can have as many as you like for any number of customers - even if they're the same - you'll just need to muck about with your primary keys a bit.

For example if you had a triple PK on name, 1st line of address and addresstype, you wouldn't be able to duplicate the same addresstype AND the same address for the same customer.
 
That's OK - you can have as many as you like for any number of customers - even if they're the same - you'll just need to muck about with your primary keys a bit.

For example if you had a triple PK on name, 1st line of address and addresstype, you wouldn't be able to duplicate the same addresstype AND the same address for the same customer.


I have attached picture of current view of database, hope this helps in what I am trying to acheive?
 

Attachments

  • Untitled-2 copy.jpg
    Untitled-2 copy.jpg
    69.6 KB · Views: 106
Er yep nice form, one table would do the job nicely there.
 
Primary Keys shouldn't be an issue.
Just have fields in your CustomerTable as:

PostAdd1, PostAdd2, PostAdd3, DelAdd1, DelAdd2, DelAdd3 and reference the fields in your query to collect data for your Invoice Header.

If you do have customers like WalMart then you could have a DelAddTable with delivery addresses as records and each record will have a field CustomerID. This would be One CustomerID (CustTable) record to Many DelAddID (DelAddTable) records and your invoice header will store the DelAddID field.
An invoice to WalMart will only show WalMart Delivery Addresses to select from.
 
PKs were just to stop dupes is all. The point I was trying to make is that it would be easier in the long run just to have all customer contact info in the one table.
Hullstorage, think of each table as a different subject - contacts, products, transactions and so on.
 
Er yep nice form, one table would do the job nicely there.


I dont think this will work, could you have a look at the attachement of database to give more of an idea and tell me if i am heading in the right path?

thanks

simon
 

Attachments

I can't open them from here sorry - what I meant is, all the contact details should be kept in one table, with a field to identify the contact record type - be it invoice, delivery, or whatever.

Then, you'd base your form on a query that pulled back the customer details and whatever info you wanted from other tables.

Like I said earlier, each table should be treated as a different subject. That's how I organise my DBs anyway.....
 

Users who are viewing this thread

Back
Top Bottom