Help with simple sales data base

  • Thread starter Thread starter CP
  • Start date Start date
C

CP

Hi need help with a school project for one of my off spring ;)
They are writing a simple sales database and have become stuck. Any help
would be appreciated.

We have an Orders table, Customers table (with a link to orders) and a
product table.

What we are trying to do is have an orders form where you select a customer
(done that), and have a subreport pick out the products you are selling -
however I cant figure out how to link products to orders as i may have many
lines of products on one order and wish to show totals etc?

Any pointers....
 
Hi need help with a school project for one of my off spring ;)
They are writing a simple sales database and have become stuck. Any help
would be appreciated.

We have an Orders table, Customers table (with a link to orders) and a
product table.

What we are trying to do is have an orders form where you select a customer
(done that), and have a subreport pick out the products you are selling -
however I cant figure out how to link products to orders as i may have many
lines of products on one order and wish to show totals etc?

Any pointers....
Click to expand...

You need one more table: Orderlines.

In your order table you store general data about orders (date,
customer). In the orderlines table (linked to the order table and you
product table) you store a rcord for each product in an order
(product, quantity).

Groeten,

Peter
http://access.xps350.com
 
You will want to add a 4th table combining Orders and Products. In its
simplest form you can use OrderID and ProductID to make your primary key.
In this way, you can show what products were on a specific order.

Sharkbyte
 
An order form is not really that simple. It has a few tricks.

I would go with the tblOrderLines approach. Subform for orderlines where
you use a combobox to pick the product (tblProduct) on each line, you also
enter the quantity. When you pick the product use extra hidden columns in
the combobox to help populate the product price and product description, let
the user enter quantity and possible a checkbox for taxable or not.

The totals at the bottom of the order are usually the trickiest parts.
Since it's for school I'll let you figure that one out yourself.

If you can't figure it out shoot me an email I'll give you more pointers.
Download www.donationmanagementsoftware.com and look at the invoice section
for an example of the invoice form (very similar concept to order). You can
view the back-end to see/steal table structures.

Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com
 
Hi need help with a school project for one of my off spring ;)
They are writing a simple sales database and have become stuck. Any help
would be appreciated.

We have an Orders table, Customers table (with a link to orders) and a
product table.

What we are trying to do is have an orders form where you select a customer
(done that), and have a subreport pick out the products you are selling -
however I cant figure out how to link products to orders as i may have many
lines of products on one order and wish to show totals etc?

Any pointers....
Click to expand...

Take a look at the Northwind sample database that comes with Access. It
implements exactly this. It's not ideal, and there are some features I don't
like, but you can use it as a sample of how to do this. (Hint: you need
another table, related one to many to Orders and also to Products; it's called
OrderDetails in Northwind).
 
CP said:
Hi need help with a school project for one of my off spring ;)
They are writing a simple sales database and have become stuck. Any help
would be appreciated.

We have an Orders table, Customers table (with a link to orders) and a
product table.

What we are trying to do is have an orders form where you select a
customer
(done that), and have a subreport pick out the products you are selling -
however I cant figure out how to link products to orders as i may have
many
lines of products on one order and wish to show totals etc?

Any pointers....
Click to expand...
 
Back
Top