Access novice - table relationships help

[Shoo]

Hi, I am currently stuck on making a working one to many table relationship, I will post what I have so far, what I am stuck on is forming a proper working relationship.


tblCustomers
CustomerID
Name
Address(street)
City
Region
PostCode
Telephone
Discount %

tblOrders
OrderID
ProductID
Description
Qty
UnitPrice

tblProduct
ProductID
Description
Room
Finish
UnitPrice
Stock Qty

The desired output is a working order form, that when, for example you enter the product ID into the appropriate field automatically brings the rest of the product fileds such as description and unit price. And when the customer ID is put into the appropriate field all of the relative customer info is shown.

I am new to access and I apologise if my post is unclear or incomplete, any input is appreciated.

Thanks in advance.

 

[ColinEssex]

Firstly, you'll need the CustomerID in the Orders table or you won't link a customer to an order.

Then you don't need the description or price in the orders table as its in the products table - you can pull that in later in a query. Presumably you'll have OrderDate, OrderNumber etc in the orders table.

Your search method you describe can be achieved by using a ComboBox, create it with the wizard and select the option 3 (find record based on selection)

Alternatively, you can use the QuickSearch example in the sample databases for a more exotic search method.

Personally, I would also have an issues table linked to the Orders table by OrderID - one order can have many issues on it. The issues table would then hold the quantity ordered etc, i.e all data associated with the issue, not the order. Then have the CustomerID and OrderID as Autonumbers and primary keys.

Incidentally, have you considered what would happen if someone orders 100 of something and you only have 75 in stock? (a part order) and how about delivery details like DeliveryDate etc?

Col

 

[Shoo]

Thanks for the info. I am trying to get the basics working first then I am going to add int he additional dynamics.

I am getting confused on the order form when using the wizard I am not quite sure which compnents to pull in from the three tables and in what order, any suggestions?

Thanks.

 

[ColinEssex]

Three tables? if you're referring to your productID search then you only need the Products table.

[edit] I would use queries as the recordsource for your forms as they're more flexible

Col

 

[Shoo]

Thanks for the info.

Below is attached the table relationships at present, how does it look? Any amendments?

 

[ColinEssex]

why have an employees table? the fields look the same as the customers table except for salary.

Col

 

[Shoo]

Each employee is assigned to a customer and they receive 10% commission from the sales.

Are the relationships workable?

And in what order would I pull the fields from the relative tables when designing a form with the wizard? Sorry I get really confused at this point.

Thanks again.

 

[ColinEssex]

Oh right, I understand, I thought they were customers also.

For your input form recordsource just create queries. Do the customers first with their demographic data then do the orders etc.

Col

 

[Pat Hartman]

The orders table needs to be broken into two tables.
tblOrder
OrderID (autonumber, primary key)
CustomerID (number, foreign key to tblCustomer)
EmployeeID (number, foreign key to tblEmployee)
DiscountPct (copied from tblCustomer)
OrderPlacedDt
OrderShippedDt

tblOrderDetail
OrderDetailID (autonumber, primary key)
OrderID (number, foreign key to tblOrder)
ProductID (number, foreign key to tblProduct)
UnitPrice (copied from tblProduct)
Qty

Notice that proper names do NOT contain spaces or special characters so remove the % and spaces from your names. Also, do not use names like "Name" since it is the name of a property do not use function names such as "Date" and "Time". Property and function names are considered reserved words and will cause problems in VBA.