New to Access 2007 :would like some advice / guidance - thanks

[stephengrenfell]

Hello,

I am new to Access 2007 but I have used Access before (albeit a long time ago) and have some basic skills in html/php and I am quite familiar with Excel tables etc.. so I am not a complete novice.
I have a web site with a simple shopping cart function that can export a csv file. I want to use Access to create my invoices, send a confirmation email for each order and create shipping label list (export csv file).
The csv file from my shopping cart contains a row for each item purchased on each order. Each row contains several fields for the customer’s details (that they have entered in a form on the web site) and several fields for the item (product code, quantity, unit price, etc..). So for example if a customer orders 3 items his/her details appear in each of 3 consecutive rows (1 row per item) in the csv file.
I understand that in Access I will need to create several tables
- Customers (contains their details address, email, tel etc..)
- Invoice (invoice number, date, customer number, etc..)
- Products (product ref, description, unit price, vat rate, weight, etc..)
- Several small tables for : payment methods, VAT rates, shipping methods, shipping rates etc..
The things I am uncertain about are :
- When importing the csv file (from my shopping cart) I want it create the customer record and add it to the customer table but how do I deal with consecutive rows containing identical customer fields ?.
- Each invoice has multiple items – where / how should I store these. Should there be some kind of daughter table (of the invoice table) and what should the field relationships be ?. This I am very unclear about.
- Each of the items has a serial number and I want to add this manually to the customer’s invoice – where should I store this (in which table) ?
Any help / guidance / would be appreciated.
Thanks

 

[DannySeager]

I would suggest you do it like this.

Create a table that you import your CSV File into, once you've processed the CSV file you clear the table out for the next day

When importing the csv file (from my shopping cart) I want it create the customer record and add it to the customer table but how do I deal with consecutive rows containing identical customer fields ?

Create a query based on the CSV File table.. something like

SELECT DISTINCT CustomerName, Address1, Address2 FROM TblCSVImport WHERE CustomerName NOT IN (SELECT CustomerName FROM TblCustomers)

This will only show 1 record per customer where the customer Name does not exist in your customers table.

Each invoice has multiple items – where / how should I store these. Should there be some kind of daughter table (of the invoice table) and what should the field relationships be ?.

You need an invoice Table

TblInvoice
InvoiceID (PK - Autonumber)
InvoiceNumber
CustomerID
CustomerAddress
InvoiceDate

and you need an invoiceItems Table

TblInvoiceItems
InvoiceItemID (PK - Autonumber)
InvoiceID (FK)
ProductID (PK)
Quantity
PricePerItem
SerialNumber
Other fields

Each of the items has a serial number and I want to add this manually to the customer’s invoice – where should I store this (in which table) ?
Any help / guidance / would be appreciated.

You would store it in the invoice items table. Can you not automate the serial number... is it not recorded anywhere that you could make it look up to?