Query returns numbers insread of data

[billheath]

I have a form of sales data for various products. When a new product is entered into the form, the supplier is also entered from a combo box. The combo box is populated by a supplier table [sup_tbl]. I want to be able to see a record of each product which indicates the supplier source. However, when I run the query, the supplier list contains only a number (Not the name). The underlying table shows the correct vendor's name.
The number that is shown in the query seems to come from an Access derived sequence beginning with 0. (Not the primary key, auto number.)

I'm not sure how to force the query to indicate the name instead of the number. I tried adding the [sup_tbl] to the querry and linking them but it produces a data type error. the following is the sql statement:

SELECT Prod_Data.[Closed Date], Prod_Data.Prod_Name, Prod_Data.Symbol, Prod_Data.Rec_By, FROM Prod_Data
ORDER BY Prod_Data.Closed DESC , Prod_Data.Prod_Name;

Thanks for your help, Bill

 

[Andrzejek]

How does Prod_Data table relate to sup_tbl table?
Which field in Prod_Data table equals to which field in sup_tbl table?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[billheath]

Thanks for responding. the field Prod_Data.Rec_ByIs related in the 2 tables. However, when I try to get Access to relate them in the query, I get the data type error.
Bill

 

[SkipVought]

Have you checked the Data Type of the fields that are to be joined in the 2 tables?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[billheath]

Both are Text fields

Bill

 

[SkipVought]

Please post a congruent sample of the data in both tables and identify the join field in each table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MajP]

For your combo box please post the following
From the Data Tab
Control Source:
Row Source:
Bound Column:
From the Format Tab
Column Count:
Column Widths:

 

[billheath]

Control Source: Rec_By
Row Source: sup_tbl
Bound Column: 0
From the Format Tab
Column Count: 2
Column Widths: 2.5",0.5"

 

[dhookom]

Change the Bound Column to 1. Column 0 is indeed the index order of the items in the combo box. If you are using "Lookup" fields in Access, you should reconsider since they are often confusing since you see a text value from a related table while storing a primary key value (often numeric).

Duane
Hook'D on Access
MS Access MVP

 

[billheath]

When I change the bound column to 1; my forms displays the vendor number, not the name. I am trying to make both the form and the query display the name.

Thanks

 

[MajP]

if you want to store the ID but show the name you would have something like

Select SupID, SuppName from tableSuppliers

then the combo would be
bound Column: 1
column count: 2
column widths: 0", 1"

so the bound column is the id and gets stored in the table. The only visible column is the name.

 

[billheath]

Thanks, I now have the suppliers name on the form and the id stored in the table. However my query still shows only the id, not the name. And, when I try to add the supplier table to the query, I still get a "Type Mismatch in expression" error.

 

[billheath]

SkipVought asked that I send a sample of the 2 tables.

This is a simplified representation of the 2 tables. The actual tables have several hundred entries. The relationship is set with [Sup_tbl.ID] to [Prod_Data.[Rec_By]



Prod_Data
Data Type Text Text Date Date Number Currency
Prod_Name Symbol Closed_Date Open Rec_By Price
Bearing Be10 2/24/2011 0 6
Bearing Be09 2/24/2011 0 4.5
Grease As-99 1/8/2012 1 6.89
1/4" Fitting Tm-67 12/8/2012 1 2.49


[Sup_tbl]
Data Type auto Number Text
ID Suppl
0 Dixie
1 GXD

 

[dhookom]

Apparently your fields don't match data types in the primary/foreign keys. I would expect you need to change the Prod_Data table structure.


Duane
Hook'D on Access
MS Access MVP