How To get a spesific Item Price From a Table

[LOIZOS]

Hi,

I have two Tables . Customers and Items.
In the Items Table I have multiple Selling Prices for each Item.(Whole sale Price, Cash Price,,,,)
In Customer Table I have a field which indicates what kind of price I will sale to him.
When I am selling an item to the customer I Would like to pup up the correct selling Price.

Pls Help

Thank in Advance

[June7]

Need to know more about data structure.

These prices are in separate fields? How many are there?

Items table is the table with pricing information?

What table has item sales records?

[LOIZOS]

The Prices are in 5 Different fields(Selling_Price1 , Selling_Price2...)
I have also Sales-Header Table (Fields [Invoice_no] [invoice_date] [Customer_code])
and Sales-Details Table (fields([Item-Code] [Qty sold] And [Selling Price])
When I type the Item Code In a Combo box i Get also all the information from the Item Table.
I need to get the correct price for the specific customer

[June7]

Are SalesHeader and SalesDetails set up as form/subform?

Include the Customers table in the RecordSource of form for SalesHeader, join type "Include all records from SalesHeader and only those from Customers that match.

Include the Prices table in the RecordSource of form for SalesDetails, join type "Include all records from SalesDetails and only those from Prices that match.

Expression in textbox named Type on SalesDetails form to retrieve the pricing type.

=Forms!SalesHeader!PriceType

Expression in textbox on SalesDetails form to retrieve price:

=Switch([Type]="Wholesale", [Selling_Price1], [Type]="Cash", [Selling_Price2], etc)

[LOIZOS]

The SalesHeader and SalesDetails set up as form/subform

Include the Customers table in the RecordSource of form for SalesHeader, join type "Include all records from SalesHeader and only those from Customers that match.
I did not understand it.
Relationship between Customers Table and Sales Header Table connected with Price_Type Field???

[June7]

Aren't Customers table and SalesHeader related by CustomerID?

I am suggesting RecordSource for the forms be a query that joins tables. Are you familiar with queries that join tables? Basic functionality of relational database.

If you want to provide db for analysis, follow instructions at bottom of my post.

[LOIZOS]

SCS_SYSTEM.zip


Dear June7

Pls Find Attached my DB. The main idea is that
an Item has multiple selling prices(Selling_price1...) and the customers has predefine what type of Selling price will buy the products.

When I choose the item in the subform the correct price will pop up.
Thanks

[June7]

The PriceType fields in Items table are useless.

RecordSource for Frm_Invoices_Header
SELECT [Invoices-Header].*, Customers.Price_Type
FROM Customers RIGHT JOIN [Invoices-Header] ON Customers.Customer_Code = [Invoices-Header].Customer_Code;

Barcode is primary key in Items but Item_Code is saved as foreign key in Invoices_Details. The primary key should be saved as foreign key. Either make Item_Code the primary key or save the barcode into Invoices_Details.

RecordSource for Frm_Invoices_Details
SELECT [Invoices-Details].*, Items.Selling_Price1, Items.Selling_Price2, Items.Selling_Price3, Items.Selling_Price4, Items.Selling_Price5
FROM Items RIGHT JOIN [Invoices-Details] ON Items.Item_Code = [Invoices-Details].Item_Code;

Expression in Type textbox on Frm_Invoices_Details
=[Forms]![frm_Invoices_Header]![Price_Type]

Expression for the pricing in Text20
=Switch([Type]="P1",[Selling_Price1], [Type]="P2",[Selling_Price2], [Type]="P3",[Selling_Price3], [Type]="P4",[Selling_Price4], [Type]="P5",[Selling_Price5])

[LOIZOS]

Thanks
Is Working OK!!!

[orange]

Loizos,

When you see fields such as Price_Type1,Price_Type2,Price_Type3...Selling_Pric e1,Selling_Price2,Selling_Price3, it begs for Normalization.
You may find these videos of interest.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming