query question - Microsoft Access / VBA

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

Subscribe Post Reply

1974

Delerna

1,134

Expert 1GB

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

Dec 23 '09 #2

NeoPa

32,556

Expert Mod 16PB

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

 SELECT ...,

       [Species Table].Species_Number,

       ...

FROM TableX INNER JOIN [Species Table]

  ON TableX.???=[Species Table].Species_Code

     ...

Dec 24 '09 #3

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

Expand|Select|Wrap|Line Numbers

 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));

Dec 28 '09 #4

Delerna

1,134

Expert 1GB

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

 
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));

then [Fish Length Log].Species_Code is text and this is undesirable

and when you do this

Expand|Select|Wrap|Line Numbers

 
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));

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

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

Dec 30 '09 #6

Delerna

1,134

Expert 1GB

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

 
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));

Dec 30 '09 #7

sillyr

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

1,134

Expert 1GB

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

32,556

Expert Mod 16PB

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

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.

Jan 2 '10 #11

NeoPa

32,556

Expert Mod 16PB

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

Similar topics

Fine tuning this sql-query. help!

by: majsen | last post by:

Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in...

Microsoft SQL Server

Query performance question on a large table

by: Együd Csaba | last post by:

Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...

PostgreSQL Database

Pulling the Record Number into a Query - Attempt 2

by: John Ortt | last post by:

> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record...

Microsoft Access / VBA

Filling question ID automatically by using query or VBA

by: Ekqvist Marko | last post by:

Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...

Microsoft Access / VBA

1 SP with dynamic input parameters and multiple rows as the source of the query

by: serge | last post by:

How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...

Microsoft SQL Server

Use Select Query variable against a table and return value to same select query

by: jjturon | last post by:

Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...

Microsoft Access / VBA

Help trying to limit the results on a 3-table query?

by: mmitchell_houston | last post by:

I'm working on a .NET project and I need a single query to return a result set from three related tables in Access 2003, and I'm having trouble getting the results I want. The details: ...

Microsoft Access / VBA

Query for Month

by: Stan | last post by:

I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...

Microsoft Access / VBA

How to use a saved cross-tab query in a report?

by: Richard Hollenbeck | last post by:

I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few...

Microsoft Access / VBA

duplicates query help & strategy for update queries with SetWarnings = False

by: ARC | last post by:

Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...

Microsoft Access / VBA

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General