1 Field and 2 Criteria

I'm new to Access but very fluent in Excel so bare with me on this.

What I have is a query I'm trying to create where I have a Problem
Code(numeric) and a Fix Code(numberic). Each of these codes have a Long
Description in a table just for these codes. However, this table has several
"TABL"'s within it. To get descriptions for a Problem Code the TABL field
must equal "101". To get descriptions for a Fix Code the TABL field must
equal "303". This is a problem because when I write the query I have each
code read out on a row but I can only assign TABL to either 101 or 303 and
Long Description will show up twice with the same description.

This is basically what my query looks like right now:

SELECT C.ACCOUNT, W.PROBCODE, D.LONGDESC, W.FIXCODE, D.LONGDESC
FROM CUSTOMER AS C, WORKORDER AS W, DESCTABLES AS D
WHERE D.TABL = "101"

But instead of getting a Problem Code desctiption and a Fix Code description
I get 2 Problem Code descriptions.

You have to join the tables in the query. Customer must be joined to
Workorder table.

I could do more than guess if I saw a sample of your data but try this --
SELECT C.ACCOUNT, W.PROBCODE, D.LONGDESC, W.FIXCODE, D.TABL, D.LONGDESC
FROM CUSTOMER AS C INNER JOIN WORKORDER AS W ON W.ACCOUNT = C.ACCOUNT,
DESCTABLES AS D
WHERE W.FIXCODE = D.FIXCODE OR W.PROBCODE = D.PROBCODE
ORDER BY C.ACCOUNT, W.PROBCODE, D.TABL;

--
Build a little, test a little.


"jace" wrote:

I'm new to Access but very fluent in Excel so bare with me on this.

What I have is a query I'm trying to create where I have a Problem
Code(numeric) and a Fix Code(numberic). Each of these codes have a Long
Description in a table just for these codes. However, this table has several
"TABL"'s within it. To get descriptions for a Problem Code the TABL field
must equal "101". To get descriptions for a Fix Code the TABL field must
equal "303". This is a problem because when I write the query I have each
code read out on a row but I can only assign TABL to either 101 or 303 and
Long Description will show up twice with the same description.

This is basically what my query looks like right now:

SELECT C.ACCOUNT, W.PROBCODE, D.LONGDESC, W.FIXCODE, D.LONGDESC
FROM CUSTOMER AS C, WORKORDER AS W, DESCTABLES AS D
WHERE D.TABL = "101"

But instead of getting a Problem Code desctiption and a Fix Code description
I get 2 Problem Code descriptions.

Thanks for the reply. I don't know that this will work how I need but that's
probably like you said because I can't provide sample data. I'm thinking
what I will do is just make 2 new tables, 1 for Problem Codes and 1 for Fix
Codes. Again thanks for your response and help.

"KARL DEWEY" wrote:

You have to join the tables in the query. Customer must be joined to
Workorder table.

I could do more than guess if I saw a sample of your data but try this --
SELECT C.ACCOUNT, W.PROBCODE, D.LONGDESC, W.FIXCODE, D.TABL, D.LONGDESC
FROM CUSTOMER AS C INNER JOIN WORKORDER AS W ON W.ACCOUNT = C.ACCOUNT,
DESCTABLES AS D
WHERE W.FIXCODE = D.FIXCODE OR W.PROBCODE = D.PROBCODE
ORDER BY C.ACCOUNT, W.PROBCODE, D.TABL;

--
Build a little, test a little.


"jace" wrote:

I'm new to Access but very fluent in Excel so bare with me on this.

What I have is a query I'm trying to create where I have a Problem
Code(numeric) and a Fix Code(numberic). Each of these codes have a Long
Description in a table just for these codes. However, this table has several
"TABL"'s within it. To get descriptions for a Problem Code the TABL field
must equal "101". To get descriptions for a Fix Code the TABL field must
equal "303". This is a problem because when I write the query I have each
code read out on a row but I can only assign TABL to either 101 or 303 and
Long Description will show up twice with the same description.

This is basically what my query looks like right now:

SELECT C.ACCOUNT, W.PROBCODE, D.LONGDESC, W.FIXCODE, D.LONGDESC
FROM CUSTOMER AS C, WORKORDER AS W, DESCTABLES AS D
WHERE D.TABL = "101"

But instead of getting a Problem Code desctiption and a Fix Code description
I get 2 Problem Code descriptions.