By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

query question

P: 37
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
Dec 23 '09 #1
Share this Question
Share on Google+
11 Replies


Delerna
Expert 100+
P: 1,134
can you post your query.......and maybe some sample data
Dec 23 '09 #2

NeoPa
Expert Mod 15k+
P: 31,186
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT ...,
  2.        [Species Table].Species_Number,
  3.        ...
  4. FROM TableX INNER JOIN [Species Table]
  5.   ON TableX.???=[Species Table].Species_Code
  6.      ...
Dec 24 '09 #3

P: 37
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [Fish Length Log].ID,
  2.        [Fish Length Log].TRIP5 AS TRIP,
  3.        [Fish Length Log].HAUL,
  4.        [Fish Length Log].Species_Code,
  5.        [Fish Length Log].LF_DISCARD_CODE,
  6.        [Fish Length Log].WEIGHT_TYPE,
  7.        [Fish Length Log].SUBSAMPLE_WEIGHT_lbs,
  8.        [Fish Length Log].SIZE_CM10,
  9.        [Fish Length Log].SIZE_CM11,
  10.        [Fish Length Log].SIZE_CM12,
  11.        [Fish Length Log].SIZE_CM13,
  12.        [Fish Length Log].SIZE_CM14,
  13.        [Fish Length Log].SIZE_CM15,
  14.        [Fish Length Log].SIZE_CM16,
  15.        [Fish Length Log].SIZE_CM17,
  16.        [Fish Length Log].SIZE_CM18,
  17.        [Fish Length Log].SIZE_CM19,
  18.        [Fish Length Log].SIZE_CM20,
  19.        [Fish Length Log].SIZE_CM21,
  20.        [Fish Length Log].SIZE_CM22,
  21.        [Fish Length Log].SIZE_CM23,
  22.        [Fish Length Log].SIZE_CM24,
  23.        [Fish Length Log].SIZE_CM25,
  24.        [Fish Length Log].SIZE_CM26,
  25.        [Fish Length Log].SIZE_CM27,
  26.        [Fish Length Log].SIZE_CM28,
  27.        [Fish Length Log].SIZE_CM29,
  28.        [Fish Length Log].SIZE_CM30,
  29.        [Fish Length Log].SIZE_CM31,
  30.        [Fish Length Log].SIZE_CM32,
  31.        [Fish Length Log].SIZE_CM33,
  32.        [Fish Length Log].SIZE_CM34,
  33.        [Fish Length Log].SIZE_CM35,
  34.        [Fish Length Log].SIZE_CM36,
  35.        [Fish Length Log].SIZE_CM37,
  36.        [Fish Length Log].SIZE_CM38,
  37.        [Fish Length Log].SIZE_CM39,
  38.        [Fish Length Log].SIZE_CM40,
  39.        [Fish Length Log].SIZE_CM41,
  40.        [Fish Length Log].SIZE_CM42,
  41.        [Fish Length Log].SIZE_CM43,
  42.        [Fish Length Log].SIZE_CM44,
  43.        [Fish Length Log].SIZE_CM45,
  44.        [Fish Length Log].SIZE_CM46,
  45.        [Fish Length Log].SIZE_CM47,
  46.        [Fish Length Log].SIZE_CM48,
  47.        [Fish Length Log].SIZE_CM49,
  48.        [Fish Length Log].SIZE_CM50,
  49.        [Fish Length Log].SIZE_CM51,
  50.        [Fish Length Log].SIZE_CM52,
  51.        [Fish Length Log].SIZE_CM53,
  52.        [Fish Length Log].SIZE_CM54,
  53.        [Fish Length Log].SIZE_CM55,
  54.        [Fish Length Log].SIZE_CM56,
  55.        [Fish Length Log].SIZE_CM57,
  56.        [Fish Length Log].SIZE_CM58,
  57.        [Fish Length Log].SIZE_CM59,
  58.        [Fish Length Log].SIZE_CM60,
  59.        [Fish Length Log].SIZE_CM61,
  60.        [Fish Length Log].SIZE_CM62,
  61.        [Fish Length Log].SIZE_CM63,
  62.        [Fish Length Log].SIZE_CM64,
  63.        [Fish Length Log].SIZE_CM65,
  64.        [Fish Length Log].SIZE_CM66,
  65.        [Fish Length Log].SIZE_CM67,
  66.        [Fish Length Log].SIZE_CM68,
  67.        [Fish Length Log].SIZE_CM69,
  68.        [Fish Length Log].SIZE_CM70,
  69.        [Fish Length Log].SIZE_CM71,
  70.        [Fish Length Log].SIZE_CM72,
  71.        [Fish Length Log].SIZE_CM73,
  72.        [Fish Length Log].SIZE_CM74,
  73.        [Fish Length Log].SIZE_CM75,
  74.        [Fish Length Log].SIZE_CM76,
  75.        [Fish Length Log].SIZE_CM77,
  76.        [Fish Length Log].SIZE_CM78,
  77.        [Fish Length Log].SIZE_CM79,
  78.        [Fish Length Log].SIZE_CM80,
  79.        [Fish Length Log].SIZE_CM81,
  80.        [Fish Length Log].SIZE_CM82,
  81.        [Fish Length Log].SIZE_CM83,
  82.        [Fish Length Log].SIZE_CM84,
  83.        [Fish Length Log].SIZE_CM85,
  84.        [Fish Length Log].SIZE_CM86,
  85.        [Fish Length Log].SIZE_CM87,
  86.        [Fish Length Log].SIZE_CM88,
  87.        [Fish Length Log].SIZE_CM89,
  88.        [Fish Length Log].SIZE_CM90,
  89.        [Fish Length Log].SIZE_CM91,
  90.        [Fish Length Log].SIZE_CM92,
  91.        [Fish Length Log].SIZE_CM93,
  92.        [Fish Length Log].SIZE_CM94,
  93.        [Fish Length Log].SIZE_CM95,
  94.        [Fish Length Log].SIZE_CM96,
  95.        [Fish Length Log].SIZE_CM97,
  96.        [Fish Length Log].SIZE_CM98,
  97.        [Fish Length Log].SIZE_CM99,
  98.        [Fish Length Log].SIZE_CM100,
  99.        [Fish Length Log].SIZE_CM101,
  100.        [Fish Length Log].SIZE_CM102,
  101.        [Fish Length Log].SIZE_CM103,
  102.        [Fish Length Log].SIZE_CM104,
  103.        [Fish Length Log].SIZE_CM105,
  104.        [Fish Length Log].SIZE_CM106,
  105.        [Fish Length Log].SIZE_CM107,
  106.        [Fish Length Log].SIZE_CM108,
  107.        [Fish Length Log].SIZE_CM109,
  108.        [Fish Length Log].SIZE_CM110,
  109.        [Fish Length Log].SIZE_CM111,
  110.        [Fish Length Log].SIZE_CM112,
  111.        [Fish Length Log].SIZE_CM113,
  112.        [Fish Length Log].SIZE_CM114,
  113.        [Fish Length Log].SIZE_CM115,
  114.        [Fish Length Log].SIZE_CM116,
  115.        [Fish Length Log].SIZE_CM117,
  116.        [Fish Length Log].SIZE_CM118,
  117.        [Fish Length Log].SIZE_CM119,
  118.        [Fish Length Log].SIZE_CM120,
  119.        [Fish Length Log].SIZE_CM121,
  120.        [Fish Length Log].SIZE_CM122,
  121.        [Fish Length Log].SIZE_CM123,
  122.        [Fish Length Log].SIZE_CM124,
  123.        [Fish Length Log].SIZE_CM125,
  124.        [Fish Length Log].SIZE_CM126,
  125.        [Fish Length Log].SIZE_CM127,
  126.        [Fish Length Log].SIZE_CM128,
  127.        [Fish Length Log].SIZE_CM129,
  128.        [Fish Length Log 130+cm].SIZE_CM130,
  129.        [Fish Length Log 130+cm].SIZE_CM131,
  130.        [Fish Length Log 130+cm].SIZE_CM132,
  131.        [Fish Length Log 130+cm].SIZE_CM133,
  132.        [Fish Length Log 130+cm].SIZE_CM134,
  133.        [Fish Length Log 130+cm].SIZE_CM135,
  134.        [Fish Length Log 130+cm].SIZE_CM136,
  135.        [Fish Length Log 130+cm].SIZE_CM137,
  136.        [Fish Length Log 130+cm].SIZE_CM138,
  137.        [Fish Length Log 130+cm].SIZE_CM139,
  138.        [Fish Length Log 130+cm].SIZE_CM140,
  139.        [Fish Length Log 130+cm].SIZE_CM141,
  140.        [Fish Length Log 130+cm].SIZE_CM142,
  141.        [Fish Length Log 130+cm].SIZE_CM143,
  142.        [Fish Length Log 130+cm].SIZE_CM144,
  143.        [Fish Length Log 130+cm].SIZE_CM145,
  144.        [Fish Length Log 130+cm].SIZE_CM146,
  145.        [Fish Length Log 130+cm].SIZE_CM147,
  146.        [Fish Length Log 130+cm].SIZE_CM148,
  147.        [Fish Length Log 130+cm].SIZE_CM149,
  148.        [Fish Length Log 130+cm].SIZE_CM150,
  149.        [Fish Length Log 130+cm].SIZE_CM151,
  150.        [Fish Length Log 130+cm].SIZE_CM152,
  151.        [Fish Length Log 130+cm].SIZE_CM153,
  152.        [Fish Length Log 130+cm].SIZE_CM154,
  153.        [Fish Length Log 130+cm].SIZE_CM155,
  154.        [Fish Length Log 130+cm].SIZE_CM156,
  155.        [Fish Length Log 130+cm].SIZE_CM157,
  156.        [Fish Length Log 130+cm].SIZE_CM158,
  157.        [Fish Length Log 130+cm].SIZE_CM159,
  158.        [Fish Length Log 130+cm].SIZE_CM160,
  159.        [Fish Length Log 130+cm].SIZE_CM161,
  160.        [Fish Length Log 130+cm].SIZE_CM162,
  161.        [Fish Length Log 130+cm].SIZE_CM163,
  162.        [Fish Length Log 130+cm].SIZE_CM164,
  163.        [Fish Length Log 130+cm].SIZE_CM165,
  164.        [Fish Length Log 130+cm].SIZE_CM166,
  165.        [Fish Length Log 130+cm].SIZE_CM167,
  166.        [Fish Length Log 130+cm].SIZE_CM168,
  167.        [Fish Length Log 130+cm].SIZE_CM169,
  168.        [Fish Length Log 130+cm].SIZE_CM170,
  169.        [Fish Length Log 130+cm].SIZE_CM171,
  170.        [Fish Length Log 130+cm].SIZE_CM172,
  171.        [Fish Length Log 130+cm].SIZE_CM173,
  172.        [Fish Length Log 130+cm].SIZE_CM174,
  173.        [Fish Length Log 130+cm].SIZE_CM175,
  174.        [Fish Length Log 130+cm].SIZE_CM176,
  175.        [Fish Length Log 130+cm].SIZE_CM177,
  176.        [Fish Length Log 130+cm].SIZE_CM178,
  177.        [Fish Length Log 130+cm].SIZE_CM179,
  178.        [Fish Length Log 130+cm].SIZE_CM180,
  179.        [Fish Length Log 130+cm].SIZE_CM181,
  180.        [Fish Length Log 130+cm].SIZE_CM182,
  181.        [Fish Length Log 130+cm].SIZE_CM183,
  182.        [Fish Length Log 130+cm].SIZE_CM184,
  183.        [Fish Length Log 130+cm].SIZE_CM185,
  184.        [Fish Length Log 130+cm].SIZE_CM186,
  185.        [Fish Length Log 130+cm].SIZE_CM187,
  186.        [Fish Length Log 130+cm].SIZE_CM188,
  187.        [Fish Length Log 130+cm].SIZE_CM189,
  188.        [Fish Length Log 130+cm].SIZE_CM190,
  189.        [Fish Length Log 130+cm].SIZE_CM191,
  190.        [Fish Length Log 130+cm].SIZE_CM192,
  191.        [Fish Length Log 130+cm].SIZE_CM193,
  192.        [Fish Length Log 130+cm].SIZE_CM194,
  193.        [Fish Length Log 130+cm].SIZE_CM195,
  194.        [Fish Length Log 130+cm].SIZE_CM196,
  195.        [Fish Length Log 130+cm].SIZE_CM197,
  196.        [Fish Length Log 130+cm].SIZE_CM198,
  197.        [Fish Length Log 130+cm].SIZE_CM199,
  198.        [Fish Length Log 130+cm].SIZE_CM200
  199.  
  200. FROM   [Fish Length Log 130+cm] LEFT JOIN
  201.        [Fish Length Log]
  202.   ON   [Fish Length Log 130+cm].ID = [Fish Length Log].ID
  203.  
  204. WHERE  ((([Fish Length Log 130+cm].ACCEPTABLE)=No)
  205.   AND  (([Fish Length Log].ACCEPTABLE)=No));
Dec 28 '09 #4

Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [Fish Length Log].TRIP5 AS TRIP,   
  2.        [Fish Length Log].Species_Code
  3. FROM [Fish Length Log 130+cm] 
  4. LEFT JOIN [Fish Length Log] ON [Fish Length Log 130+cm].ID = [Fish Length Log].ID
  5. WHERE ((([Fish Length Log 130+cm].ACCEPTABLE)=No) 
  6.   AND (([Fish Length Log].ACCEPTABLE)=No)); 
  7.  
then [Fish Length Log].Species_Code is text and this is undesirable

and when you do this
Expand|Select|Wrap|Line Numbers
  1. SELECT [Fish Length Log].TRIP5,   
  2.        [Fish Length Log].Species_Code
  3. FROM [Fish Length Log 130+cm] 
  4. LEFT JOIN [Fish Length Log] ON [Fish Length Log 130+cm].ID = [Fish Length Log].ID
  5. WHERE ((([Fish Length Log 130+cm].ACCEPTABLE)=No) 
  6.   AND (([Fish Length Log].ACCEPTABLE)=No)); 
  7.  
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?
Dec 28 '09 #5

P: 37
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
Dec 30 '09 #6

Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [Fish Length Log].TRIP5 AS TRIP,    
  2.        cint([Fish Length Log].Species_Code) as  Species_Code
  3. FROM [Fish Length Log 130+cm]  
  4. LEFT JOIN [Fish Length Log] ON [Fish Length Log 130+cm].ID = [Fish Length Log].ID 
  5. WHERE ((([Fish Length Log 130+cm].ACCEPTABLE)=No)  
  6.   AND (([Fish Length Log].ACCEPTABLE)=No));  
  7.  
Dec 30 '09 #7

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

the table is set up like this

Species_Code Species_Code_Name
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_Name when I make the changes I mentioned already. Hope that helps.
Dec 31 '09 #8

Delerna
Expert 100+
P: 1,134
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_Name] 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.
Dec 31 '09 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 2 '10 #10

P: 37
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.
Jan 2 '10 #11

NeoPa
Expert Mod 15k+
P: 31,186
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 ;)
Jan 3 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.