My First Access Database

[replicant00A52]

Hi,

I am new to access and want to create a relational database with the following attributes/functions:

- Customer Database

• store customer details (FirstName, LastName, Address, PhoneNumber etc.)
• link customers to products purchased

- Purchase Order Forms; records to be added and edited by new users via forms

• search function to find existing customers records
• add purchase detail to existing customer records

- Create reports (mail merge labels) based on defined criteria such as location and purchase history
- UI must be very user friendly for staff

I am familiar with excel so I can create the tables without to much trouble. The forms however are causing me a lot of grief. The form wizard creates a form that searches my customer table record by record, but I have no idea how to make it add data to a purchase order table and create the relationship between customer and product.
I have watched copious youtube videos on MS Access with limited success. If anyone can offer assistance or point me in the right direction I would really appreciate it.

Thanks

[Bob Fitz]

...I am familiar with excel so I can create the tables without to much trouble....

IMHO familiarity with excel alone will not be enough to design your tables as they should be. Access is a different animal altogether. Many development problems stem from poor table structure and design. So, can you show us your tables and fields showing the primary and foreign keys.

[pbaldy]

In case you're looking for confirmation, my first thought was the same as Bob's. Familiarity with Excel is almost a liability when it comes to properly designing tables in a relational database. One of many links on normalization:

Fundamentals of Relational Database Design -- r937.com

[replicant00A52]

In case you're looking for confirmation, my first thought was the same as Bob's. Familiarity with Excel is almost a liability when it comes to properly designing tables in a relational database. One of many links on normalization:

Fundamentals of Relational Database Design -- r937.com

Thanks guys, I just bought a book titled 'Access 2010 Bible' by Michael R. Groh. Can anyone recommend this book? Tables to follow.

Thanks again,

[pbaldy]

I haven't read it, so can't offer an opinion.

[replicant00A52]

ok here is a break down my planned table structure;

CustomerTable
- CustomerID (primary key, auto number)
- FirstName (text)
- LastName (text)
- Address (text)
- City (text)
- Province (text)
- PostalCode (text)
- PhoneNumber (text)
- DOB (date/time)
- Notes (memo)

ProductTable
- ProductID (primary key, auto number)
- TourName (text)
- Days (number)
- DepartureDate (date/time)
- SglPrice (currency)
- DblPrice (currency)
- TrplPrice (currency)

SalesTable
- SaleID (primary key, auto number)
- CustomerID (foreign key)
- ProductID (foreign key)
- Purchase Date (date/time)

My intention is to have customer data entered (and added to) when sales are made. I have created a many to many relationship between customers and products but have had very limited success developing forms. Any help is greatly appreciated. Please note that I'm a bit of a computer noob.

[pbaldy]

Unless a sale can only comprise one product, the normalized approach would be a "SalesDetails" table for the products. The SalesTable would not contain any product info. The details table would be related one-to-many to the sales table on the SaleID and to the products table on ProductID.

[replicant00A52]

typically sales will be one product (98% of the time) and not multiple products at once.

[pbaldy]

Unless it's 100%, you want the other table. I'd probably add the other table even if I thought it was 100%, since "things change".

[RayMilhon]

I would also recommend against using structure like single price, double price, triple price. I would use 2 fields Qty, Price. Assuming that double and triple prices were included to give a discount create a function that applies the discount. For example:

Single Price 20.00 each
Double Price 18.00 each
Triple Price 15.00 Each

Public function discount(ByRev price as currency, qty as long) as currency

Select Case qty
      case is = 1
            discount = price
      Case is = 2
            discount = price * .9
      Case Is > 2
            discount = price * .75
      Case Else
            discount = 0
end Select

The case else is there just in case there's a 0 or less qty

[pbaldy]

Ray makes a good point, and I'd go a step further and say it should all be in a table. I wouldn't have something like discount percentages in code, given their propensity to change over time.

[replicant00A52]

also discount will not be formula driven. It will be on a product by product basis. In this case it is tour packages so travelers who travel by themselves pay a premium as they are not sharing a room with a partner. I will submit a table with your suggested changes for sales details.

[RayMilhon]

In that case you'll want a product discount look up table along the lines of

productid number Foreign key to product table
qty Number the level the discount is applied at
discount Number can be a percentage or a fixed amount but it needs to be consistent throughout the table

You will also need to add a qty field to the sales table.

You would link the product id to the products table and the qty to the sales table. that will get your discount

[replicant00A52]

In that case you'll want a product discount look up table along the lines of

productid number Foreign key to product table
qty Number the level the discount is applied at
discount Number can be a percentage or a fixed amount but it needs to be consistent throughout the table

You will also need to add a qty field to the sales table.

You would link the product id to the products table and the qty to the sales table. that will get your discount

'add a qty field to the sales table. link the product id to the products table and the qty to the sales table.' are your instructions to link the qty to the sales table even though it is already in the sales table? I am confused.

revised table structure;

CustomerTable
- CustomerID (primary key, auto number)
- FirstName (text)
- LastName (text)
- Address (text)
- City (text)
- Province (text)
- PostalCode (text)
- PhoneNumber (text)
- DOB (date/time)
- Notes (memo)

ProductTable
- ProductID (primary key, auto number)
- TourName (text)
- Days (number)
- DepartureDate (date/time)

SalesTable
- SaleID (primary key, auto number)
- CustomerID (foreign key)
- ProductID (foreign key)
- Purchase Date (date/time)
- TravelersQty (number) to reference pricing on ProductDiscountTable?

ProductDiscountLookupTable
- ProductDiscountID (primary key, auto number)
- SaleID (foreign key)
- ProductID (foreign key)
- SglPrice (currency)
- DblPrice (currency)
- TrplPrice (currency)

[RayMilhon]

You need to correct your table structure.

the productDiscountlookuptable has too many fields it should only have 3

Productid
Qty
DiscountRate

Product Table should not have departure date that should be in the sales table
Product Table should have a price field for the base price of each tour

Sglprice, dblprice,trplprice should go away and never return.