Looking information from one table to another..

[Guest]

I know that you can relate to tables and with a query you can merge it. But
I'm trying to merge to tables and still add records to the main table. I
have to tables, one with all the information of my company and my products
and the other with the pricing list. I'm trying to merge those two tables so
if I wirte the product in the main table it show the price. Can someone help
me on this one and so please show me how to do it.

 

[Steve Schapel]

Victor,

Can you please post back with a list of the fields in both tables. At
this stage I would guess that the design of your tables might need
reviewing.

 

[Guest]

Ok, I have two tables. One with the list of price and the other one with the
data. example:
Field "A" and "B" from table 1(Price list)
Field "C" From table 2(Data table)

Field "C" from table 2 have this info:

1.Toyota
2.Honda
3.Mitsubichi
4.Toyota
5.Daewoo

In field "A" and "B" from table 1 I have:

"A" "B"
Toyota $50.00
Honda $60.00
Mitsubichi $40.00
Daewoo $30.00

I need to create a query that show me the table 2 with the matching price
from table 1 where "C" is from table 2 and "B" is the match from table 1:

"C" "B"
1.Toyota $50.00
2.Honda $60.00
3.Mitsubichi $40.00
4.Toyota $50.00
5.Daewoo $30.00

I know that I have to create a relationship with both tables but what type?

 

[Steve Schapel]

Victor,

Ok, thanks for the additional explanation.

1. Make a new query in design view (click New from the Queries tab in
the Database Window, and select Design View).
2. From theh 'Show Table' window that pops up, add both table 1 and
table 2 to the query, then close the Show Table window.
3. In the query design window, click on the field "C" from table 2 and
drag/drop to field "A" in table 1. This should result in a line drawn
between the tables.
4. Add the fields you want to the query design grid, i.e. field "C"
from table 2 and field "B" from table 1.

You can then make a form based on this query. When you enter an entry
in field "C" in a new record, the corresponding amount from field "B"
should be shown automatically.

In your description, you showed the data in table 2 with numbers in
front of the names of the car brands, whereas in table 1 there are no
numbers. This method I have described above will only work where the
data in field "C" and field "A" is *exactly* the same.

The next step might be to put a combobox on the form for the field "C"
data, so that the entry of the car make is consistent and accurate.

I have also assumed that for any entry you will put in field "C", there
will always be an existing corresponding entry in table 1.

 

[Guest]

Thanks Steve... that help...

Victor,

Ok, thanks for the additional explanation.

1. Make a new query in design view (click New from the Queries tab in
the Database Window, and select Design View).
2. From theh 'Show Table' window that pops up, add both table 1 and
table 2 to the query, then close the Show Table window.
3. In the query design window, click on the field "C" from table 2 and
drag/drop to field "A" in table 1. This should result in a line drawn
between the tables.
4. Add the fields you want to the query design grid, i.e. field "C"
from table 2 and field "B" from table 1.

You can then make a form based on this query. When you enter an entry
in field "C" in a new record, the corresponding amount from field "B"
should be shown automatically.

In your description, you showed the data in table 2 with numbers in
front of the names of the car brands, whereas in table 1 there are no
numbers. This method I have described above will only work where the
data in field "C" and field "A" is *exactly* the same.

The next step might be to put a combobox on the form for the field "C"
data, so that the entry of the car make is consistent and accurate.

I have also assumed that for any entry you will put in field "C", there
will always be an existing corresponding entry in table 1.