How can I extract other field values on a table knowing the value of one field

[Schultzy]

I have two tables:

The first table is called:
Products
it has the following fields
ProdName
ProdId
ProdType
ProdTaxes

The second table is called:
ProductInput
with the following fields
ProductId
Quantity
Price
TaxPercentage
...and others...

I have made a form based on table 'ProductInput'
I use a combo to get the ProdId (Products table) by means of
ProdName (Products table) and I put it in ProductId (ProductInput table)

My question is:
How can I get the corresponding record in the table 'Products' using
ProductId (ProductInput table), to extract the other fields (like ProdType
and ProdTaxes -Product table-) and put them in the fields TaxPercentage,
Price, etc of ProductInput table.

[NeoPa]

Try using this SQL (or something derived therefrom) as the basis for your form rather than a simple link to the table :

SELECT *
FROM [Products] LEFT JOIN [ProductInput] ON Products.ProdId = ProductInput.ProductId

[Schultzy]

I am sorry but I am very new at this
and I do not understand the idea
Where should I put this code?
And could I get a thorough explanation of what it does?

Thanks a lot!

[NeoPa]

Edit the form in Design View.
Look at properties of the form.
Near the top there is a Record Source property.
Select this and enter the SQL from previous post or your modified version.
Press F1 for help on the Record Source property.

This should get you going.

[Schultzy]

At the moment it is not working!
It says that the query does not exist!
Maybe I have not spelt it correctly!

Why the asterisc?

Thanks for your time

[Schultzy]

Excuse me!
I had spelt it badly!
Now it gives no error

But I do not know how to go on

What do I have to write in Control Source of the button TaxPercentage
to get the value of ProdTaxes of the table Products?

This is very confusing, I'm sorry!
Thanks in advance

[NeoPa]

The asterisk (*) means to return all fields available.
In the Control Source property of your TaxPercentage control (TextBox) drop down the list and you should see the field you need in the list.

[Schultzy]

It surely works now!!
I am obliged
Thank you very much
I must check everything more thoroughly and try to understand
what I have done

So If I want to get other values from other tables I just should
add the tables in this query, I suppose!
I'll give it a try

I'll keep in touch for further help!
Bye

[NeoPa]

...and to help you with the SQL you can design the query in Access design view then either :
1. Get the SQL from the query (SQL View) and paste it in or
2. Just use the Query name instead of the SQL in the RecordSource of the form.

[Schultzy]

I have taken a look at the record source
and I have pressed the 3 dotted button to see
how the query has been done but
there is nothing written there.

Is this normal?

So if I must create the query from Access in design view
What must I write?

I do not understand much the LEFT JOIN and ON commands
even though I have read an SQL Tutorial about it.

Thanks!

[Schultzy]

OK!
Forget the last post
I have worked a little with it and now I think that I understand
I have added another relation to the query and now
I also can get values from this new table

It seems to work but I'm sure it will come up with other problems
because I have to add many other things

It's been a great help, thanks indeed!

Another question!!!!
I had posted other messages but I think they're not
much understandable, so is there a way to delete them?

Bye

[NeoPa]

Identify anything you would like deleted (Post #) and I will do it for you (Only moderators and admins can do that).

[Schultzy]

I think I have another problem here!

Now, I get the values from another table perfectly well (Products)
and I see these values in the form
but they are not present in the form's table! (ProductInput)

I set Control source to the field of the other table (Products) from where I
want the value taken
but even though the value can be seen in the form,
it is not inserted in the table (ProductInput)

I do not know wether this is clear enough!
Thanks

[NeoPa]

What are you using as the RecordSource of the Form?
(Any Filter info would be useful too).

[Schultzy]

In the record source of the form I use the query in SQL you gave me.
I have added other tables but it seems to work perfectly

But now I am aware that I can get the 'ProdTaxes' from 'Products' table
and I can see it in the 'ProductInput' form
but I would like to keep it in 'TaxPercentage' field of 'ProductInput' table

Another example:
Imagine I have a table with three fields:
Quantity
Price
Total
If I set the control for the Total field (control source =[Quantity]*[Price]
then when I'm in the form I see the result but It is not entered in the
table

Maybe confusing!
Thanks

[NeoPa]

In the record source of the form I use the query in SQL you gave me.
I have added other tables but it seems to work perfectly

But now I am aware that I can get the 'ProdTaxes' from 'Products' table
and I can see it in the 'ProductInput' form
but I would like to keep it in 'TaxPercentage' field of 'ProductInput' table

Another example:
Imagine I have a table with three fields:
Quantity
Price
Total
If I set the control for the Total field (control source =[Quantity]*[Price]
then when I'm in the form I see the result but It is not entered in the
table

Maybe confusing!
Thanks

So, to avoid scrolling up and down too much, here is the code again.

SELECT *
FROM [Products] LEFT JOIN [ProductInput] 
ON Products.ProdId = ProductInput.ProductId

And to show the table layouts again for the same reason :

Products
    ProdName
PK    ProdId
    ProdType
    ProdTaxes
 
ProductInput
FK    ProductId
    Quantity
    Price
    TaxPercentage
...and others...

For Referential Integrity it is not advised (No - it's advised NOT) to duplicate data.
Duplication of data means that there is an extra (unnecessary) burden to synchronise this data.
So, you DON'T want to store Products.ProdTaxes and ProductInput.TaxPercentage as the same value. Decide where the data should reside (is it static for a product or does it change across Inputs?), and leave it only there.
This is equally true of a Total field within a single record. Not a good idea to store, but fine to show on a form.

HTH.

[Schultzy]

The Total field was just an example!
I do not have to include it in the table because everytime
I may need it, all I have to do is re-calculate again!

The problem is:
Products.ProdTaxes could be equal to 18%

So all the products that have this percentage will be calculated
when I make the Invoice with the same value
I do not include the 18% in the ProductInput table because as
you say I can calculate it everytime is needed

But after a year has passed, the percentage could change.
Let's see the taxes could increment to 19.5%

I must change the percentage value (ProdTaxes) in order that the new
Invoices are calculated accordingly

I am afraid the old Invoices will also change the value to the new
percentage and this would be a fatal error

I hope you understand the problem!
Thanks!