Help with relationships / new database project

[hullstorage]

i am quite new to this so please go easy on me!!!
i have looked at northwind but all looks a bit complicated at the moment

right here goes!

i am trying to create a database to enter multiple orders on and then print invoices on the program at a later date, these are the fields i am working with:


please bare in mind that the customer will place multiple orders daily
and is not invoiced until the end of the month:

how do i link these to work with each other

does anyone have a small sample database they can let me view or download:


[customer table]
acc no
customer name
address
telephone no


[invoice table]
invoice no
invoice date
customer
£ amount
£ vat
£ gross

[order table]
order date
job no
acc no
customer name
job details
customer ord no
£ amount
£ vat
£ gross

 

[irish634]

Make the "Customer Name" in your customer table Indexed with no duplicates (or make it a primary key)

Link "Customer Name" in the customer table to the customer name in the other tables, (One to many, enforce referential integrity, and cascade update).

That should do it.

 

[hullstorage]

Make the "Customer Name" in your customer table Indexed with no duplicates (or make it a primary key)

Link "Customer Name" in the customer table to the customer name in the other tables, (One to many, enforce referential integrity, and cascade update).

That should do it.

it not doing nothing even though i have followed your steps

what i want it to do is at the end of the month recall all the jobs for a given customer and store them somehow in the invoice table and give all the jobs 1 invoice number

thanks any ideas

 

[Rabbie]

As you may have more than 1 customer with the same name, I would add a CustomerID field to the Customer table and use that for linking. Numeric fields take up less space than text fields so make for better indexes.

 

[hullstorage]

As you may have more than 1 customer with the same name, I would add a CustomerID field to the Customer table and use that for linking. Numeric fields take up less space than text fields so make for better indexes.

hi there

these are the same customer not customers with same name

i have got it to link customers and deliveries but what do i do in the invoice table and the relationship

 

[Rabbie]

hi there

these are the same customer not customers with same name

i have got it to link customers and deliveries but what do i do in the invoice table and the relationship

All you need to do to establish a relationship between Customer and Invoice is to store the Primary key of the Customer in the Invoice record as a Foreign key. Then you can find all the invoices for a particular customer using a query.

 

[hullstorage]

All you need to do to establish a relationship between Customer and Invoice is to store the Primary key of the Customer in the Invoice record as a Foreign key. Then you can find all the invoices for a particular customer using a query.

i have got these to link

but how do i allocate the invoice number from the invoice table to the deliveries table

 

[daviedaft]

i have got these to link

but how do i allocate the invoice number from the invoice table to the deliveries table

You should have a field for Invoice number in the Deliveries table, which would be a foreign key to the Invoice table. Based on what you've got above, i'd do something like:

[Deliveries table]
delivery no (PRIMARY KEY - Autonumber)
invoice no (FOREIGN KEY - number (long integer) - link to invoice table)
customer no (FOREIGN KEY - number (long integer) link to customer table)
DeliveryScheduledTime
DeliveryDriver
etc etc

Just for reference - it's best practise not to name fields with spaces in them. I'd have DeliveryID, InvoiceID, CustomerID etc. Probably not gonna make a massive deal of difference for what you're doing but I believe in doing it right (or as right as possible) first time and learning properly

 

[hullstorage]

You should have a field for Invoice number in the Deliveries table, which would be a foreign key to the Invoice table. Based on what you've got above, i'd do something like:

[Deliveries table]
delivery no (PRIMARY KEY - Autonumber)
invoice no (FOREIGN KEY - number (long integer) - link to invoice table)
customer no (FOREIGN KEY - number (long integer) link to customer table)
DeliveryScheduledTime
DeliveryDriver
etc etc

Just for reference - it's best practise not to name fields with spaces in them. I'd have DeliveryID, InvoiceID, CustomerID etc. Probably not gonna make a massive deal of difference for what you're doing but I believe in doing it right (or as right as possible) first time and learning properly

OK got that, but wont this only give me one invoice number per delivery i enter

what i need is say if i enter 15 deliveries for 1 customer i need 1 invoice number for all the deliveries

what i am trying to acheive is simliar to sage

 

[Rabbie]

If You put the same Invoice number into more than 1 delivery then you will achieve what you want. This works because the Invoiceno is a foreign key in the delivery record. Its the same technique as having the customer number in several delivery records.

See this link for more info

 

[hullstorage]

If You put the same Invoice number into more than 1 delivery then you will achieve what you want. This works because the Invoiceno is a foreign key in the delivery record. Its the same technique as having the customer number in several delivery records.

See this link for more info

i have uploaded file so could you please tell me where i am going wrong
i still get 1 invoice per delivery

thanks
simon