Query showing ID not text

  • Thread starter Thread starter George
  • Start date Start date
G

George

I have a cbo which allows the user to select an enquiry
type on a form. The cbo is based on a query with Date of
Enquiry from tblEnquirer and Enquiry type from
tblEnquiryType, as I wish to limit the date range in the
query. Both tables are linked via EnquiryTypeID. The cbo
on the form runs well but the EnquiryTypeID autonumber is
saved to the table, not the text. Therefore, my report on
Date vs Enquiry Type provides only dates vs autonumbers.

Hope this makes sense and thanks for any help.
 
I have a cbo which allows the user to select an enquiry
type on a form. The cbo is based on a query with Date of
Enquiry from tblEnquirer and Enquiry type from
tblEnquiryType, as I wish to limit the date range in the
query. Both tables are linked via EnquiryTypeID. The cbo
on the form runs well but the EnquiryTypeID autonumber is
saved to the table, not the text. Therefore, my report on
Date vs Enquiry Type provides only dates vs autonumbers.

Hope this makes sense and thanks for any help.
Click to expand...

Storing the autonumber IS CORRECT. That's what you *want* it to do!

Base your Report on a Query joining tblEnquirer to tblEnquiryType;
select the desired fields from tblEnquirer and the enquiry text from
tblEnquiryType. Storing the text redundantly in tblEnquirer is neither
necessary nor good design!
 
To explain John's and Duane's statements a little further a combo boxed used
for lookup will always store the Primary key id in the table. This is what
links the field to any other related tables with the same id. If you want to
show the actual text then you need to use the appropriate tables, adding the
necessary field in a query that links to your report. You won't use a table
alone.
 
Thank you for all the responses.
I had tried basing the report on a query linking both
tables as per advice below. The problem was nothing would
happen would I ran the query. As mentioned, the correct
EnquiryTypeID is stored in tblEnquiry, but for some
reason the query will not work. I have tried changing the
relationship fields, etc etc. I know it must be something
relatively simple which I am overlooking, but I can't
work it out.
Grateful any suggestions.
 
Thank you for all the responses.
I had tried basing the report on a query linking both
tables as per advice below. The problem was nothing would
happen would I ran the query. As mentioned, the correct
EnquiryTypeID is stored in tblEnquiry, but for some
reason the query will not work. I have tried changing the
relationship fields, etc etc. I know it must be something
relatively simple which I am overlooking, but I can't
work it out.
Click to expand...

First step:

Get rid of ALL YOUR LOOKUP FIELDS. If you haven't already done so, see
http://www.mvps.org/access/lookupfields.htm for some reasons why most
developers avoid this nightmarish misfeature.

Second step: create a Query joining your main table to tblEnquiry by
EnquiryTypeID (drag EnquiryTypeID from your main table to
EnquiryTypeID in tblEnquiry). Select the enquiry text field from
tblEnquiry.

If this doesn't make sense, or doesn't work, please post:

- the SQL view of the query
- The relevant field names, datatypes, and meanings of the fields from
your two tables
- A specific description of what "doesn't work" - what happens? what
do you expect to happen?
 
tblEnquirer has
EnquirerID : PK Autonumber
FirstName -Txt
LastName
Address
City
Postcode
State
Country
Email
Phone
EnquiryDate - D/T
EquiryTypeID - No.
EnquiryType - Txt

tblEnquiryType
EnquiryTypeID : PK AN
EnquiryType Txt

The query is constructed as you have mentioned below with
EnquiryDate from tblEnquirer and EnquiryType from
tblEnquiryType (joined via enquiryTypeID).

All the data for both fields has been previously entered
on the database. When I run the query I expect two
columns of data (datasheet view) with the Enquiry Date
and the types of Enquiries for that date. However, my
query produces nothing, but the headers for both columns.
I want to be able to base a report on this query.
Hope this makes sense
thanks
 
EquiryTypeID - No.
EnquiryType - Txt

tblEnquiryType
EnquiryTypeID : PK AN
EnquiryType Txt
Click to expand...

Is EnquiryType a field in BOTH tables?? It shouldn't be; it should
exist ONLY in tblEnquiryType. Again - DO YOU HAVE THIS AS A LOOKUP
FIELD? if so - *kill it*. Lookup fields do far more harm than good,
and I think this problem is just one example.

Try copying and pasting this query into the SQL window of a new Query:

SELECT tblEnquirer.*, tblEnquiryType.EnquiryType
FROM tblEnquirer INNER JOIN tblEnquiryType
ON tblEnquiryType.EnquiryTypeID = tblEnquirer.EnquiryTypeID;

What do you see?
 
tks John
What I see is a datasheet with columns/headers for all
fields from both tables, but no data.
 
tks John
What I see is a datasheet with columns/headers for all
fields from both tables, but no data.
Click to expand...

That suggests that the EnquiryTypeID field in tblEnquiry doesn't
contain any values which link to tblEnquiryTypes. Open each table
independently and look at the actual (NOT LOOKUP!) values - do they
match? What does tblEnquirer.EnquiryType contain? The field should not
exist, since EnquiryText should be obtained by having links to
tblEnquiryTypes.
 
John tks.
the problem was having tblEnquirer.EnquiryType as txt. I
thought the cbo would need to place txt into this field
in this table. But it works via the EnquiryTypeID link.
thanks for the learning experience.
 
Back
Top