table relationship

[Guest]

Hi,
i want to create a price list table and a customer table. I can run a query
using primary/foreign keys to return customer details, qty ordered and price
to pay,and design a form to input data but this only works on the first
record.
How can i get the query/form to work with more customer orders?

price list
ID autonumber
price 1
price 2
price 3

Customer
Id autonumber
surname
forename
ect
qty of 1
qty of 2
qty of 3.
I am a novice in access so any ideas would be gratefull.
Bob

 

[Guest]

I am not sure of what you are trying to accomplish here. Do you want to enter
customer orders in your database or are you trying to create a database in
which you can manage pricelists bases on quantities ordered? In both cases I
fear your approach is not the best way to do this.

Let me know what you're trying to accomplish so perhaps I can help you with
it.

 

[Guest]

Hi Bob

Looking at the table layout you have shown, it is slightly confusing about
what the database will do.
In your customer table you shown "only" customer details such as name
address etc and not qty of 1/2/3 etc
You should have a products table with details of the products and (possibly)
price (if these do not change)
You could then have a "Sales" table which would tie the other tables
together and contain such things as date, amounts paid, oweing, sales staff
(in this case you could have a tblStaff), etc, etc

eg.
tblCustomers. Details + CustID
tblProducts. Details + ProdID
tblSales. ProdID and CustID

Not really sure what you are trying to get from your query/report/form etc.
So if you could post more details I am sure that you will get some better
answers.

 

[Guest]

Hi Gerwin,
thank you for your response.
I will try to be a bit clearer on what i want.
A customer details form with name address ect and Qty of golf green fees
purchased
1= early
2= mid
3= late
on a seperate table price for 1, price for 2 and 3, which can be changed
from time to time.
In the form with customer details the price will display Qty*price, and
total box.
In query i can enter all details for first customer and price and total will
display, but not any more customers.
I have figured that this is because the id key for price table is 1, as only
one record entered, but i need to match this single record with any record i
input via form to customer table.
i can however do all of this in excel, of which i am confident with(and have
code to maintain excel database) but it is time i learned how to use access
properly.
I hope this makes sense and once again thank you for your aid on this matter.
Regards
Bob

 

[Guest]

This is not an answer but a related type question.
Would this be along the same line as a relationship between products and
vendors. In the the products table you have ProductID along with vaarious
other attributes, and the VendorID. In the vendor table you would have
VendorID along with the varius attributes of a Vendor. Then you would have a
table Vendor/Products that would include Vendor/ProductID, VendorID and
ProductID so a Vendor could have many products and a product could have many
Vendors. What other attributes would you want in this table?

CoachBarkerOJPW

 

[Guest]

Hi coach,
This is probably a juvinile question, but how would i link these tables
together?
What i would like to achieve is one form to input customer details, how many
green fees purchased and total to pay, but with just customer details
returning to table to establish a database. The total to pay is just visual,
as the only data for report purposes is customer details and qty of fees
purchased, the answer is probably staring me in the face, and is due to poor
table construction, and lack of thought.
Thank you for your response
regards
Bob

 

[Guest]

Yes in a way

I would have
tblProducts and tblVendors.
The link would be the Product ID.

You could create queries/reports to show which vendors sell which products
and which products are sold by which vendors.

You could also have a tblSales which would hold information (point of sale)
which hold details of the actual passing of the products - either by vendor
or product

So

Apples - sold by vendor A = 12
Apples - sold by vendor B = 20
Apples - sold by vendor C = 30
Apples - sold by vendor D = 0
Total apples sold = XXX
Give Vendor C a bonus
Give Vendor D a telephone call


Or (from the same tables)

Vendor A sold on 28/12/2006 12 apples
Vendor A sold on 28/12/2006 56 Pears
Vendor A sold on 28/12/2006 0 hats
Vendor A sold on 28/12/2006 2 cabage
Value of total products = £XXX
Total products sold = XXX

and (assuming you have a stock level)
Apples in store = XXX - Reorder
Pears in Store = XXX - Reorder
Hats in store = XXX - Do Not reorder

etc
etc
etc

 

[BV2312]

Hi Bob
It looks like you need to spilt your table a bit more:

Green table:
GreenID
GreenFee

GreenSales:
CustomerId
GreenID
SaleDate
Etc

Customer Table:
CustomerID
Name
Adddress
ETC

you can link them by using the 'relationship' Icon this is located in the
toolbar next the help icon or
Tools then Relationship add all the table you need then link them up with
the fields:

so GreenSale.customerID would be linked to Customer.customerID
and GreenSale.greenID would be linked to green.greenID


Hope this helps

Regards

 

[Guest]

Yes
that makes a lot of sense
Thank you

Hi Bob
It looks like you need to spilt your table a bit more:

Green table:
GreenID
GreenFee

GreenSales:
CustomerId
GreenID
SaleDate
Etc

Customer Table:
CustomerID
Name
Adddress
ETC

you can link them by using the 'relationship' Icon this is located in the
toolbar next the help icon or
Tools then Relationship add all the table you need then link them up with
the fields:

so GreenSale.customerID would be linked to Customer.customerID
and GreenSale.greenID would be linked to green.greenID


Hope this helps

Regards

 

[BV2312]

No Problems

Yes
that makes a lot of sense
Thank you

 

[Guest]

So in table Vendor/Product there would only be two fields and ProductId would
be the Primary key and it should be set to number(long)?

Would the information in the ne table be auto enerated then by what is in
the other two tables?