ms access relation problem

Explain how your business works because that will determine the answer.  

For example, do all sales get associated with customers (ie. in a grocery store the only sales that get associated with customers are ones where a customer swipes their frequent buyer card, but all other sales would not be associated with a customer).

Are sales in the sales table paid for immediately?  If so then why do you need a payment table?  If not, then you might consider thinking in terms of an AccountsReceivable table rather than a payment table.

Tell me what kind of business this is an how transactions are processed and I'll see if I can help you further.

The bussines is a department store i need to  make a sale table that the customet paid automaticly .Buy the products and go to paid imediately

OK.  So with your added tables you would have an orders, sales, and payments table.  Based off the fact that customers always pay immediately, it doesn't sound like you need to have all three of these tables.  If customers can do layaways then you would though.

Anyway, let's get a little more specific now.  What all information are you wanting to record in these tables?  That's a rhetorical question.  If any of the data you want to be in these tables already exists in another table then you will join the tables.  In other words, let's say you wanted to record the customer into the sales table.  Instead of duplicating customer info, you would just create an integer field that would contain the CustomerID, which would be joined to the CustomerID field in the customer table.

Now, let me know what your question(s) are in light of what I just said.  I'm sorry to draw this out, but I'm having a difficult time understanding exactly what you need.  If you already know exactly what fields should go in your sales and payment tables then you could just post that information and I would tell you where to make the joins.  But if you knew that then you probably could figure out the joins with common sense on your own, so it sounds like you need help determining what data to record.  Do you see what I mean?

1. what tables if the customer paid automaticly must have in  my database tell me your opinion please

also i want to make a form for orders so orders and orders details must be there

brandonvmoore are u here ?

Just to clarify, it doesn't matter whether they are paying 'automatically', but whether they are paying 'immediately'.  I'll assume that that's what you meant, but if not let me know.

One thing I don't know about your database is exactly what the Orders table contains.  Generally, an order is something that is not necessarily paid for up front.  And an order may differ from what actually ends up getting sold (for example, if a company is out of stock on something so they substitute a different product).  It sounds to me, though, like you don't really need an orders table because I don't think you do orders.  The Orders table you already have is probably actually your Sales table.  If this is the case then you should just continue to use this table instead of creating an entirely new sales table.  Basically, determine what needs to go in your sales table and if it could just as easily go into the orders table then use the orders table.

In any case, you Sales table will start something like this:
SaleID, CustomerID,EmployeeID, OrderID??, Total, whatever,other,financial,fields,you,need,etc.
CustomerID and EmployeeID will join their respective tables.
The question after the OrderID is because I don't know for sure if you have true orders and if so I don't know how you handle them.  However, let's just say for now that if you don't know whether you need this field then you probably don't.

Next, you will have a SalesDetail table.  This table will store the individual line items that go along with a sale.  It will have a SaleID field that will join the the Sales table.  Then it will have a LineID field that tells what line item it is on the transaction.  Then a Price field to show what the item sold for.

Now, for payments you need to remember that a customer could pay in multiple ways (ie. part on a credit card, part with a check, part with cash).  So you will have a SaleID that joins to the Sales table, a CustomerID that joins to the Customer table, a LineID (this will normally just be 1, but in the rare cases that someone uses multiple forms of payment you will have another number for each additional form of payment),

If payments are made in full up front you don't need any kind of AccountReceivable.  If you do something like layaways though then you would need it to track how much people owe you.

OK, so I'm trying to lead you in the right direction but what I've said is not all inclusive or definitive by any means.  If this is not enough information to get you going on your own then you should find a consultant to talk to you in person, or at least over the phone.

Good Luck.

ok  waait to read

Hnasr, in what way is his database not normalized?  At first glance it looks ok to me.

Also, in a POS system there are usually many times this number of tables.  I've personally worked with about 3 POS systems that had in excess of 100.  The number of tables shouldn't really be a factor because however many tables you need is how many tables you need.  But definitely do research on normalization as Hnasr suggested and try to understand it well because just knowing that will give you a better understanding of what data/tables you should add.

Good Luck!

wait a little to try it

i Need the sales table can someone make me an example based above in my database what suggested to  be inside

Primary key Sales ID
Foreing key ??
Need to  have Employee id and customer id but where the table is  must connected

make me an example pls

l have already use order table and save ddata insidee i have an order form how iused it to sales form

l have already use order table and save ddata insidee i have an order form how iused it to sales form

l have already use order table and save ddata insidee i have an order form how iused it to sales form

I have make a sugestion here  i think is what i  want

Sales Table

Sales ID as  primary key
cUSTOMER ID
EMPLOYEE ID
PRODUCT ID
payment id
TOTAL
DISCOUNT
QUANTITY
VAT
productname
product descrption


and anything else i  need in the way and a payment table

payment id as primary key
customer id
customer name
sales id
sales date
total

now i thing is ok  but if not corect me.What i want is  the relationoship where i must add these tables and the foreign keys where must be relation in order to work

below is the database now
untitled1.PNG

demoniumz,

Again, depending on what you need the database to do, this may be anything from perfect to downright harmful.

You really need to get together with a database professional, who can sit down with you and think this through carefully.
Or obtain and study a book on Database/Table Design.
Database Design for Mere Mortals:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201694719

Doing database design work beyond the basics here is what most Experts are paid to do professionally outside of this site.
So it is hard justifying doing it here for free.
It requires a lot of time, panning and forethought.
Something that is hard to get out of a Question and Answer format.

I still stand by my suggestion of going with an off-the-shelf product.
$200.
You install the software and you are up and running.
Fully tested, Money back guarantee, Full support, Upgrades, ect.
More functions than you will ever have in a home-grown system.

;-)

JeffCoachman

i make  a small project for university i must provide mine not buy and use for  profesional use

ok i  have order AS sales  need to make payment table
Payment table

Sales ID as primary key
payment id
customer id
employee id
Product id
Productname
product discription
Discount
Quantity
Total

my problem is that  i dont know where to add the relation (ok orders tables Sales ID must relate with payment table but the other foreign keys where they must related?

First of all apologize so far for my problems.I want to know 2 things nothing more.As i tell you before i  understand that must use the order form as SALES.That i want to know is  the relations.In payments table i must have Sales id (as primary key) in which table must be related. What foreign key must add in the payment tables in order to make relation  and where must be  related

can anyone drow me how is connected  and make a screenshoot

The sales form  until now is above
untitled3.PNG

Again, I cannot comment on your design.

There are just to many factors involved, to discuss in a forum like this

Please consider my suggestion of partnering with a database professional.

JeffCoachman

In the above photo can  u tell me if the database is normalize if no why??
untitled34.PNG

new table payment added that i ask you

Again,

The only criterea is if it works for you.

If it works and does what you need, then fine.

If it does not then please state "Specifically" what it does not do.

But just asking "How do I do this?" is too much of an open eneded question.

Jeff

i ask  if payments table is ok the relation one to many  and many to one.The payment table is used to save informations for the sales form in vb 2005

so i add the order foreign key of the payment  table to the primary key of the order table and the PAyment id  primary key to the customer table  foreign key

I can't tell from your diagram because the lines all converge

ok i upload it again

i put coments so now must understand from the picture
untitled34.PNG

just download one time the photo  u se the lines is ok :) and is understuntable 100%

Why can't you simply open the relationship window and move the Orders table to the right.

That is all I need.

ok i make here is 03:00 mornig in few hours  i update the question thnks for your help

upload it again
untitled1111.PNG

some lines is not appear ok if  you download  you see it complete ok

Regards demoniumz

Problem  solved by NON profesional USER by a student:)Anyway thnks  for help chhers

Thanks,

I'll check this tonight

OK,

1. One payment cannot belong to more than 1 Customer

2. I can't see a reason why Product Name, Description, Discount, or Quantity is in the Payments table.

JeffCoachman

Here is a rough sample ase well
Access-EEQ-NorthwindPaymentsTabl.mdb