Create Invoice form from multiple tables? (1 Viewer)

thudson

Old Programmer
Local time
Today, 12:59
Joined
Apr 29, 2011
Messages
68
I am trying to build a form, which will be an Invoice when I sell a product.
I have 3 tables called tbl_Customers, tbl_COrders & tbl_Parts and I have created a SELECT query with all the fields I need for the invoice. (see image).
I have created a Form which will eventually be my invoice.
When I execute the query all the fields populate the form except the customer address details?
I have foreign Key in the tbl_COrders that is one to many with the tbl_Customers table, so I expected that it would link and get the address details from there?
Can anyone see why this is not working?
Invoice Query1.jpg
Invoice Query.jpg
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:59
Joined
Sep 21, 2011
Messages
14,052
What do you see in just the query?
Why is the city in twice?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:59
Joined
Feb 19, 2002
Messages
42,981
In addition, Invoices should be main report for the customer order with a sub report for the details. Your relationship does not seem correct. Are you sure you only want to allow a SINGLE part on an order? That is very short-sighted. Even if you only allow one part on an order now, it doesn't hurt to allow for multiples so if that business rule becomes more normal, you won't have to change the application.

Also, RI is not defined for the PartNo relationship and you are inconsistent in your naming standards. If you use "fk" as the prefix for CustomerNo, then you should also use "fk" as the prefix for PartNo.

Consistency is your friend.
 

Users who are viewing this thread

Top Bottom