Solved

ms access relation problem

Posted on 2009-04-11
42
572 Views
Last Modified: 2013-11-28
hello i have a database  in ms access 2003 linked to visual basic i want to add  2 more tables  for make a form called sales. iwant to insert a payment table and a sales table but i dont know  where i must add in the relation you see in the picture also in which field must make the  relation
mydatabase.PNG
0
Comment
Question by:demoniumz
  • 24
  • 9
  • 6
  • +1
42 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
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.
0
 
LVL 5

Assisted Solution

by:brandonvmoore
brandonvmoore earned 100 total points
Comment Utility
Edit:  If you were to use an AccountReceivable table you would still have a payment table.  I just meant that it may not make sense to have a payment table without AccountReceivable.
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
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
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
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?
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
1. what tables if the customer paid automaticly must have in  my database tell me your opinion please
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
also i want to make a form for orders so orders and orders details must be there
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
brandonvmoore are u here ?
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
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.
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 200 total points
Comment Utility
There are many tables already in the database.
To add 2 more, to create a form!
You need to imagine the form, with the output required.  Normalising the database will help you know what fields to include and what relations to establish.
Look for help about normalisation.
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
ok  waait to read
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 200 total points
Comment Utility
Search the web by typing " microsoft normalization"
This is one example
http://support.microsoft.com/kb/283878
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
Hnasr, in what way is his database not normalized?  At first glance it looks ok to me.
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
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!
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
wait a little to try it
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
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

0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 200 total points
Comment Utility
demoniumz,

First, I will say that you have been given great advice so far.

As the experts have mentioned a great deal of this may end up being "Custom" so there is no real "Canned" advice anyone can give you.

As far as I can tell, the Orders Table *IS* the sales table.
Sure you can add a "Payments" table, but again, it starts to get complicated when you need to link it to various other tables.
Employees
Orders
Credit Cards
Internet Orders
Credit Card Processing
Split Payments (different Payees, different Dates)
Late Payments
Over Payments
Cancelled Orders
Refunds
International currency conversion.
...ect

The other factor is that since this is a work in progress, things can be changed or added at any time.
(Inventory, Work Orders, Projections, Profits, Financial Reports...ect)

Another line of reasoning says that you are really moving to a point where you should really consider investing in one of the hundreds of off the shelf products.
Here are two of the most Popular.
QuickBooks:
http://quickbooks.intuit.com/index.jsp

Peachtree:
http://offer.peachtree.com/ppc/?source=083C91B5CBC6495C9D02D812B03A5106&WT.srch=1&srch=google-nonbranded

These are basically turn-key systems with everything you are asking for here in a professionally designed application with:
Support, Updates, Online help, Customization, ...ect

I strongly recommend you go with something like the two products I have listed above.

Your other option is to partner with an Application Developer who specializes in this type of structure.
But this may be more expensive then buying an off the shelf product.
QuickBooks Pro is about $200 USD
The cheapest Developer could run you the same amount for 1 or 2 hours of work.

JeffCoachman
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
l have already use order table and save ddata insidee i have an order form how iused it to sales form
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
l have already use order table and save ddata insidee i have an order form how iused it to sales form
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
l have already use order table and save ddata insidee i have an order form how iused it to sales form
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
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
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
i make  a small project for university i must provide mine not buy and use for  profesional use
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
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?

0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
In the above photo can  u tell me if the database is normalize if no why??
untitled34.PNG
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
new table payment added that i ask you
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I can't tell from your diagram because the lines all converge
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
ok i upload it again
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
i put coments so now must understand from the picture
untitled34.PNG
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
just download one time the photo  u se the lines is ok :) and is understuntable 100%
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Why can't you simply open the relationship window and move the Orders table to the right.

That is all I need.
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
ok i make here is 03:00 mornig in few hours  i update the question thnks for your help
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
upload it again
untitled1111.PNG
0
 
LVL 1

Author Comment

by:demoniumz
Comment Utility
some lines is not appear ok if  you download  you see it complete ok

Regards demoniumz
0
 
LVL 1

Author Closing Comment

by:demoniumz
Comment Utility
Problem  solved by NON profesional USER by a student:)Anyway thnks  for help chhers
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Thanks,

I'll check this tonight
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Here is a rough sample ase well
Access-EEQ-NorthwindPaymentsTabl.mdb
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now