Question Relating Tables (1 Viewer)

I'm a new user to Access, and I'm using 2010 (if that helps).
I have three tables, one is called 'Customers', 'Sales' and 'Stock'.
I made a form for each of them, and at the moment I'm working on my 'Sales' form. The fields I have in this form are:
SaleID (the primary key, which is an AutoNumber)
CustomerID
StockID
Quantity
Unit Cost
Sub Total
VIP Discount?
After Discount
GST
Postage
Order Total


I have NO idea on how to get this to work. In the Relationships part, I connected the Customers primary key to the CustomerID in the Sales table. I connected the Stock primary key to the StockID in the Sales table. I turned these into Combo Boxes in the form.

When the user chooses a customer from the combo box in the Sales form, I want it to automatically fill in if the customer is a VIP or not.

In the Customers table, there is a 'VIP?' field, which is a 'Yes/No' data type. When the user picks a customer from the Combo Box in the Sales form, I want access to either tick or untick the 'VIP Discount?' checkbox based on what the Customer table has put in.

Then when they pick the Sale Item (StockID) from that combo box in the Sales form, I want access to automatically fill in the 'unit cost' field based on what the Stock item is in the Stock table.

Ok, with that in mind, I want the 'Sub total' field the calculate itself based on the values 'Quantity' x 'Unit Cost' fields. I have no idea on how to do this. Then I would like the 'After Discount' field to be automatically calculated as well, but only if the 'VIP Discount?' is ticked. The VIP Discount value is 5%. So that would mean taking the 'Sub Total' and take off 5% and presenting the result in the 'After Discount' field. Then, the GST Field should be calculated by taking a further 10% off the 'After Discount' field.

The postage is a whole other story, I might post that in another thread. The order total is GST + Postage. I have no idea how to make a calculation in Access.

If you would like me to send you my database so far or the requirements I need to make the database, I can send those.

I would really appreciate some help, I REALLY need it!!

I recommend you do some research on Normalization and Entity Relationship Diagramming in order to get your tables and relationships designed to suit your business.

Here are a number of free video tutorials related to database design concepts.

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

Also there is material here
http://www.rogersaccesslibrary.com/forum/topic238.html The first few topics are a great reference.

I would suggest you get your tables designed before you get too involved in the intricacies of forms, combo boxes etc.

You seem to be working at a very detailed level - that's good - but do you have a higher level view of what you are doing and how the pieces fit together.

Not everyone has Acc2010 or 2007, so they can not open your Accdb.

Good luck with your project.

Thanks very much for all of those links, but I don't understand what he's talking about at all :S I don't know all the terms he's using.