query question

[sillyr]

Hi - I use Access 2007. I have two tables linked by ID. I created a query to combine the two tables. My problem is that in the query my Species_Code is text, but I need it to display the number which is what is recorded in the table. I have the tables and form that users enter data into set up with a combo box where the user sees the name of the species and then the table shows the code by setting the column widths to either 1"; 0" or vise versa. I have other queries set up from these two tables that display the species code number and not the text, but cannot figure out how to get this query to do the same thing. Any help would be appreciated.
Thanks Sally

[Delerna]

can you post your query.......and maybe some sample data

[NeoPa]

This would be so much easier to understand had you included the query you were talking about in your question Sally. Without that, it's necessary for the question to be phrased very clearly and precisely. Unfortunately, that's not something everybody can manage, so the inclusion of the SQL of the query would be necessary in this case.

Trying to read between the lines, I would guess the answer to your question is simply to include the required field in the SELECT phrase of your query. With a multi-souced query as you describe, it is generally good style to include the table name in the reference too.

EG:

SELECT ...,
       [Species Table].Species_Number,
       ...
FROM TableX INNER JOIN [Species Table]
  ON TableX.???=[Species Table].Species_Code
     ...

[sillyr]

Sorry-
Here is my query. I tried to redo the query and as long as I do not make any changes to the query I get numbers in the Species_Code and not text, but I need to have TRIP5 as TRIP which is what I did in the query. When I do this the Species_Code goes back to text. Please let me know if you need anything else.
Sally

SELECT [Fish Length Log].ID,
       [Fish Length Log].TRIP5 AS TRIP,
       [Fish Length Log].HAUL,
       [Fish Length Log].Species_Code,
       [Fish Length Log].LF_DISCARD_CODE,
       [Fish Length Log].WEIGHT_TYPE,
       [Fish Length Log].SUBSAMPLE_WEIGHT_lbs,
       [Fish Length Log].SIZE_CM10,
       [Fish Length Log].SIZE_CM11,
       [Fish Length Log].SIZE_CM12,
       [Fish Length Log].SIZE_CM13,
       [Fish Length Log].SIZE_CM14,
       [Fish Length Log].SIZE_CM15,
       [Fish Length Log].SIZE_CM16,
       [Fish Length Log].SIZE_CM17,
       [Fish Length Log].SIZE_CM18,
       [Fish Length Log].SIZE_CM19,
       [Fish Length Log].SIZE_CM20,
       [Fish Length Log].SIZE_CM21,
       [Fish Length Log].SIZE_CM22,
       [Fish Length Log].SIZE_CM23,
       [Fish Length Log].SIZE_CM24,
       [Fish Length Log].SIZE_CM25,
       [Fish Length Log].SIZE_CM26,
       [Fish Length Log].SIZE_CM27,
       [Fish Length Log].SIZE_CM28,
       [Fish Length Log].SIZE_CM29,
       [Fish Length Log].SIZE_CM30,
       [Fish Length Log].SIZE_CM31,
       [Fish Length Log].SIZE_CM32,
       [Fish Length Log].SIZE_CM33,
       [Fish Length Log].SIZE_CM34,
       [Fish Length Log].SIZE_CM35,
       [Fish Length Log].SIZE_CM36,
       [Fish Length Log].SIZE_CM37,
       [Fish Length Log].SIZE_CM38,
       [Fish Length Log].SIZE_CM39,
       [Fish Length Log].SIZE_CM40,
       [Fish Length Log].SIZE_CM41,
       [Fish Length Log].SIZE_CM42,
       [Fish Length Log].SIZE_CM43,
       [Fish Length Log].SIZE_CM44,
       [Fish Length Log].SIZE_CM45,
       [Fish Length Log].SIZE_CM46,
       [Fish Length Log].SIZE_CM47,
       [Fish Length Log].SIZE_CM48,
       [Fish Length Log].SIZE_CM49,
       [Fish Length Log].SIZE_CM50,
       [Fish Length Log].SIZE_CM51,
       [Fish Length Log].SIZE_CM52,
       [Fish Length Log].SIZE_CM53,
       [Fish Length Log].SIZE_CM54,
       [Fish Length Log].SIZE_CM55,
       [Fish Length Log].SIZE_CM56,
       [Fish Length Log].SIZE_CM57,
       [Fish Length Log].SIZE_CM58,
       [Fish Length Log].SIZE_CM59,
       [Fish Length Log].SIZE_CM60,
       [Fish Length Log].SIZE_CM61,
       [Fish Length Log].SIZE_CM62,
       [Fish Length Log].SIZE_CM63,
       [Fish Length Log].SIZE_CM64,
       [Fish Length Log].SIZE_CM65,
       [Fish Length Log].SIZE_CM66,
       [Fish Length Log].SIZE_CM67,
       [Fish Length Log].SIZE_CM68,
       [Fish Length Log].SIZE_CM69,
       [Fish Length Log].SIZE_CM70,
       [Fish Length Log].SIZE_CM71,
       [Fish Length Log].SIZE_CM72,
       [Fish Length Log].SIZE_CM73,
       [Fish Length Log].SIZE_CM74,
       [Fish Length Log].SIZE_CM75,
       [Fish Length Log].SIZE_CM76,
       [Fish Length Log].SIZE_CM77,
       [Fish Length Log].SIZE_CM78,
       [Fish Length Log].SIZE_CM79,
       [Fish Length Log].SIZE_CM80,
       [Fish Length Log].SIZE_CM81,
       [Fish Length Log].SIZE_CM82,
       [Fish Length Log].SIZE_CM83,
       [Fish Length Log].SIZE_CM84,
       [Fish Length Log].SIZE_CM85,
       [Fish Length Log].SIZE_CM86,
       [Fish Length Log].SIZE_CM87,
       [Fish Length Log].SIZE_CM88,
       [Fish Length Log].SIZE_CM89,
       [Fish Length Log].SIZE_CM90,
       [Fish Length Log].SIZE_CM91,
       [Fish Length Log].SIZE_CM92,
       [Fish Length Log].SIZE_CM93,
       [Fish Length Log].SIZE_CM94,
       [Fish Length Log].SIZE_CM95,
       [Fish Length Log].SIZE_CM96,
       [Fish Length Log].SIZE_CM97,
       [Fish Length Log].SIZE_CM98,
       [Fish Length Log].SIZE_CM99,
       [Fish Length Log].SIZE_CM100,
       [Fish Length Log].SIZE_CM101,
       [Fish Length Log].SIZE_CM102,
       [Fish Length Log].SIZE_CM103,
       [Fish Length Log].SIZE_CM104,
       [Fish Length Log].SIZE_CM105,
       [Fish Length Log].SIZE_CM106,
       [Fish Length Log].SIZE_CM107,
       [Fish Length Log].SIZE_CM108,
       [Fish Length Log].SIZE_CM109,
       [Fish Length Log].SIZE_CM110,
       [Fish Length Log].SIZE_CM111,
       [Fish Length Log].SIZE_CM112,
       [Fish Length Log].SIZE_CM113,
       [Fish Length Log].SIZE_CM114,
       [Fish Length Log].SIZE_CM115,
       [Fish Length Log].SIZE_CM116,
       [Fish Length Log].SIZE_CM117,
       [Fish Length Log].SIZE_CM118,
       [Fish Length Log].SIZE_CM119,
       [Fish Length Log].SIZE_CM120,
       [Fish Length Log].SIZE_CM121,
       [Fish Length Log].SIZE_CM122,
       [Fish Length Log].SIZE_CM123,
       [Fish Length Log].SIZE_CM124,
       [Fish Length Log].SIZE_CM125,
       [Fish Length Log].SIZE_CM126,
       [Fish Length Log].SIZE_CM127,
       [Fish Length Log].SIZE_CM128,
       [Fish Length Log].SIZE_CM129,
       [Fish Length Log 130+cm].SIZE_CM130,
       [Fish Length Log 130+cm].SIZE_CM131,
       [Fish Length Log 130+cm].SIZE_CM132,
       [Fish Length Log 130+cm].SIZE_CM133,
       [Fish Length Log 130+cm].SIZE_CM134,
       [Fish Length Log 130+cm].SIZE_CM135,
       [Fish Length Log 130+cm].SIZE_CM136,
       [Fish Length Log 130+cm].SIZE_CM137,
       [Fish Length Log 130+cm].SIZE_CM138,
       [Fish Length Log 130+cm].SIZE_CM139,
       [Fish Length Log 130+cm].SIZE_CM140,
       [Fish Length Log 130+cm].SIZE_CM141,
       [Fish Length Log 130+cm].SIZE_CM142,
       [Fish Length Log 130+cm].SIZE_CM143,
       [Fish Length Log 130+cm].SIZE_CM144,
       [Fish Length Log 130+cm].SIZE_CM145,
       [Fish Length Log 130+cm].SIZE_CM146,
       [Fish Length Log 130+cm].SIZE_CM147,
       [Fish Length Log 130+cm].SIZE_CM148,
       [Fish Length Log 130+cm].SIZE_CM149,
       [Fish Length Log 130+cm].SIZE_CM150,
       [Fish Length Log 130+cm].SIZE_CM151,
       [Fish Length Log 130+cm].SIZE_CM152,
       [Fish Length Log 130+cm].SIZE_CM153,
       [Fish Length Log 130+cm].SIZE_CM154,
       [Fish Length Log 130+cm].SIZE_CM155,
       [Fish Length Log 130+cm].SIZE_CM156,
       [Fish Length Log 130+cm].SIZE_CM157,
       [Fish Length Log 130+cm].SIZE_CM158,
       [Fish Length Log 130+cm].SIZE_CM159,
       [Fish Length Log 130+cm].SIZE_CM160,
       [Fish Length Log 130+cm].SIZE_CM161,
       [Fish Length Log 130+cm].SIZE_CM162,
       [Fish Length Log 130+cm].SIZE_CM163,
       [Fish Length Log 130+cm].SIZE_CM164,
       [Fish Length Log 130+cm].SIZE_CM165,
       [Fish Length Log 130+cm].SIZE_CM166,
       [Fish Length Log 130+cm].SIZE_CM167,
       [Fish Length Log 130+cm].SIZE_CM168,
       [Fish Length Log 130+cm].SIZE_CM169,
       [Fish Length Log 130+cm].SIZE_CM170,
       [Fish Length Log 130+cm].SIZE_CM171,
       [Fish Length Log 130+cm].SIZE_CM172,
       [Fish Length Log 130+cm].SIZE_CM173,
       [Fish Length Log 130+cm].SIZE_CM174,
       [Fish Length Log 130+cm].SIZE_CM175,
       [Fish Length Log 130+cm].SIZE_CM176,
       [Fish Length Log 130+cm].SIZE_CM177,
       [Fish Length Log 130+cm].SIZE_CM178,
       [Fish Length Log 130+cm].SIZE_CM179,
       [Fish Length Log 130+cm].SIZE_CM180,
       [Fish Length Log 130+cm].SIZE_CM181,
       [Fish Length Log 130+cm].SIZE_CM182,
       [Fish Length Log 130+cm].SIZE_CM183,
       [Fish Length Log 130+cm].SIZE_CM184,
       [Fish Length Log 130+cm].SIZE_CM185,
       [Fish Length Log 130+cm].SIZE_CM186,
       [Fish Length Log 130+cm].SIZE_CM187,
       [Fish Length Log 130+cm].SIZE_CM188,
       [Fish Length Log 130+cm].SIZE_CM189,
       [Fish Length Log 130+cm].SIZE_CM190,
       [Fish Length Log 130+cm].SIZE_CM191,
       [Fish Length Log 130+cm].SIZE_CM192,
       [Fish Length Log 130+cm].SIZE_CM193,
       [Fish Length Log 130+cm].SIZE_CM194,
       [Fish Length Log 130+cm].SIZE_CM195,
       [Fish Length Log 130+cm].SIZE_CM196,
       [Fish Length Log 130+cm].SIZE_CM197,
       [Fish Length Log 130+cm].SIZE_CM198,
       [Fish Length Log 130+cm].SIZE_CM199,
       [Fish Length Log 130+cm].SIZE_CM200

FROM   [Fish Length Log 130+cm] LEFT JOIN
       [Fish Length Log]
  ON   [Fish Length Log 130+cm].ID = [Fish Length Log].ID

WHERE  ((([Fish Length Log 130+cm].ACCEPTABLE)=No)
  AND  (([Fish Length Log].ACCEPTABLE)=No));

[Delerna]

Hi Sally
Wow thats a lot of fields in that [Fish Length Log 130+cm] table.
I am going to ignore that your table does not appear to be normalized, as that is not your question.

I orriginally thought the same thing as Neopa, that you were simply selecting the incorrect field from the table.
But since your second post I am confused on what you are saying, mainly because when I think I know I say to myself....huh?? ?, that can't be right and I have a puzzled look on my face.

Anyway, I have boiled your query down to the 2 fields you are talking about
And from your second post, you seem to be saying
when you do this
[code=sql]
SELECT [Fish Length Log].TRIP5 AS TRIP,
[Fish Length Log].Species_Code
FROM [Fish Length Log 130+cm]
LEFT JOIN [Fish Length Log] ON [Fish Length Log 130+cm].ID = [Fish Length Log].ID
WHERE ((([Fish Length Log 130+cm].ACCEPTABLE)=No )
AND (([Fish Length Log].ACCEPTABLE)=No ));
[/code]
then [Fish Length Log].Species_Code is text and this is undesirable

and when you do this
[code=sql]
SELECT [Fish Length Log].TRIP5,
[Fish Length Log].Species_Code
FROM [Fish Length Log 130+cm]
LEFT JOIN [Fish Length Log] ON [Fish Length Log 130+cm].ID = [Fish Length Log].ID
WHERE ((([Fish Length Log 130+cm].ACCEPTABLE)=No )
AND (([Fish Length Log].ACCEPTABLE)=No ));
[/code]
then [Fish Length Log].Species_Code is a number, which is what you want.
Is that right?

Also when you say [Fish Length Log].Species_Code is text
do you mean that it has alpha characters in it
or do you mean that it is numerical but having text data type?

[sillyr]

Yes- sorry for the confusion, but you have it correct. When I make a change then the [Fish Length Log].Species_Code is displayed in the query as text.

Also when you say [Fish Length Log].Species_Code is text
do you mean that it has alpha characters in it
or do you mean that it is numerical but having text data type?
The [Fish Length Log].Species_Code is numerical, but has a text data type because the [Fish Length Log].Species_Code has one column as numerical values and one column as text names.
Thanks again

[Delerna]

Sorry for all the questions and no answer but I am still going Huh, with a puzzled look on my face.

The [Fish Length Log].Species_Code is numerical, but has a text data type because the [Fish Length Log].Species_Code has one column as numerical values and one column as text names.

Yes, I suspected that [Fish Length Log] had those 2 fields
1 as a number and the other as a name relating to that number.

As stated previously I orriginally thought you were simply selecting the name field instead of the number field but that doesn't appear to be the case.


Species_Code holds the number
You haven't identified the name of the field containing the text.


The problem doesn't make sense ???
In both queries you are selecting the number field.
Because of that I am having great difficulty understanding your problem.
I feel that I must be miss-understanding you somewhere.

As an example and for the sake of clarifying
one query is returning 546321 numerical
and the other is returning "546321" text

If that is correct then you could try this
[code=sql]
SELECT [Fish Length Log].TRIP5 AS TRIP,
cint([Fish Length Log].Species_Code) as Species_Code
FROM [Fish Length Log 130+cm]
LEFT JOIN [Fish Length Log] ON [Fish Length Log 130+cm].ID = [Fish Length Log].ID
WHERE ((([Fish Length Log 130+cm].ACCEPTABLE)=No )
AND (([Fish Length Log].ACCEPTABLE)=No ));
[/code]

[sillyr]

Hi - Sorry
The query is actually returning the species name for example:

the table is set up like this

Species_Code Species_Code_Na me
1200 Winter Flounder
1230 Yellowtail Flounder
1240 American Plaice
1220 Witch Flounder

When I run the query I would like the Species_code, but I am getting the Species_Code_Na me when I make the changes I mentioned already. Hope that helps.

[Delerna]

Now it is clear what you are saying
The only problem is , it is not logical !
I do not see, from the query you posted, any possible way for that to happen.
Are you sure you posted the query that was in error?
For that to happen [Species_Code_Na me] must be somewhere in your select statement.

For the two queries, as posted by me, to select the number in one instance and the name in the other is truely bizzarre.
There must be some other explanation.

[NeoPa]

I'm absolutely with Delerna on this one Sally.

I can't help feeling that the information posted is not quite correct somewhere.

I suggest you try the queries that Delerna has posted and see what results you get. Sometimes very long and complicated bits of code can have errors hidden within which you simply cannot see due to the overall complexity of the whole. It is definitely worth cutting out all the dross and looking at the heart of the matter first. Sort out the issue, then build back onto your solid foundation. It's always going to be unnecessarily complicated if you try for the whole thing in one go. Fine if it works of course, but a nightmare if it doesn't.

Let us know what results you see when trying out the versions posted by Delerna.

[sillyr]

I redid the query and it works now so I must have done something else that I did not know about. Thank you for you help.

[NeoPa]

Very pleased to hear it Sally. That was one mother of a query that I was not looking forward to getting too deeply into.

BTW Welcome to full member status ;)