Query Problem 1

[Bill4tektips]

I have an Access DB in which I am trying to construct a Query. In the first field there is the Document number followed by a number of fields 01 to 09. I want the query to take the results from a table (simple lookup with only one field "Find")then find all the corresponding fields (01 to 09)that match the Find and show against the Document number. What I am getting is the correct information but with a full list of document numbers most of which have no other info in 01 to 09. How can I get rid of document numbers that do not have an entry in 01 to 09.(See attachment)

 

[randy700]

I can't see your attachment because of firewall setup at work. I think you'll find the majority of people on this site have that situation. Try copy and paste to show your
data / examples.

A guess: WHERE Not(Somefield = "")


Randy

 

[genomon]

Actually, we can't see the attachment because it is not a valid URL.

Beir bua agus beannacht!

 

[Bill4tektips]

BMSReferenceFrom 1 2 3 4 5 6
MF B1.0002Want to get rid of these blank rows
MF B1.0003
MP B1.2001
MP B1.2004
MF B2.0001And keep these ones MP B2.0001
MF B2.0002 MP B2.0001
MF B2.0003 MP B2.0001
MF B2.0004
MF B2.0005
MF B2.0006
MF B2.0251
MF B2.0380
MF B2.1016

 

[lespaul]

you're example isn't helping any. Try to use the TGML tags to line up your data:

[tt]
Field1 Field2 Field3
data data data
[/tt]

I used the [ignore] [tt] stuff [/tt] [/ignore] tags to format the above.

Leslie

 

[PHV]

And don't forget to post your actual SQL code.

 

[Bill4tektips]

BMSReferenceFrom 1 2 3
MF B1.0002
MF B1.0003
MP B1.2001
MP B1.2004
MF B2.0001 MP B2.0001
MF B2.0002 MP B2.0001
MF B2.0003 MP B2.0001

SQL Code
SELECT tblBMS_Docs_From!BMSReferenceFrom AS BMSReferenceFrom, IIf(tblBMS_Docs_From![01]=tblFind_Data!data,tblBMS_Docs_From![01],"") AS 1, IIf(tblBMS_Docs_From![02]=tblFind_Data!data,tblBMS_Docs_From![02],"") AS 2, IIf(tblBMS_Docs_From![03]=tblFind_Data!data,tblBMS_Docs_From![03],"") AS 3, IIf(tblBMS_Docs_From![04]=tblFind_Data!data,tblBMS_Docs_From![04],"") AS 4, IIf(tblBMS_Docs_From![05]=tblFind_Data!data,tblBMS_Docs_From![05],"") AS 5, IIf(tblBMS_Docs_From![06]=tblFind_Data!data,tblBMS_Docs_From![06],"") AS 6, IIf(tblBMS_Docs_From![07]=tblFind_Data!data,tblBMS_Docs_From![07],"") AS 7, IIf(tblBMS_Docs_From![08]=tblFind_Data!data,tblBMS_Docs_From![08],"") AS 8, IIf(tblBMS_Docs_From![09]=tblFind_Data!data,tblBMS_Docs_From![09],"") AS 9, IIf(tblBMS_Docs_From![10]=tblFind_Data!data,tblBMS_Docs_From![10],"") AS 10, IIf(tblBMS_Docs_From![11]=tblFind_Data!data,tblBMS_Docs_From![11],"") AS 11, IIf(tblBMS_Docs_From![12]=tblFind_Data!data,tblBMS_Docs_From![12],"") AS 12, IIf(tblBMS_Docs_From![13]=tblFind_Data!data,tblBMS_Docs_From![13],"") AS 13, IIf(tblBMS_Docs_From![14]=tblFind_Data!data,tblBMS_Docs_From![14],"") AS 14, IIf(tblBMS_Docs_From![15]=tblFind_Data!data,tblBMS_Docs_From![15],"") AS 15, IIf(tblBMS_Docs_From![16]=tblFind_Data!data,tblBMS_Docs_From![16],"") AS 16, IIf(tblBMS_Docs_From![17]=tblFind_Data!data,tblBMS_Docs_From![17],"") AS 17, IIf(tblBMS_Docs_From![18]=tblFind_Data!data,tblBMS_Docs_From![18],"") AS 18, IIf(tblBMS_Docs_From![19]=tblFind_Data!data,tblBMS_Docs_From![19],"") AS 19, IIf(tblBMS_Docs_From![20]=tblFind_Data!data,tblBMS_Docs_From![20],"") AS 20, IIf(tblBMS_Docs_From![21]=tblFind_Data!data,tblBMS_Docs_From![21],"") AS 21, IIf(tblBMS_Docs_From![22]=tblFind_Data!data,tblBMS_Docs_From![22],"") AS 22, IIf(tblBMS_Docs_From![23]=tblFind_Data!data,tblBMS_Docs_From![23],"") AS 23, IIf(tblBMS_Docs_From![24]=tblFind_Data!data,tblBMS_Docs_From![24],"") AS 24, IIf(tblBMS_Docs_From![25]=tblFind_Data!data,tblBMS_Docs_From![25],"") AS 25, IIf(tblBMS_Docs_From![26]=tblFind_Data!data,tblBMS_Docs_From![26],"") AS 26, IIf(tblBMS_Docs_From![27]=tblFind_Data!data,tblBMS_Docs_From![27],"") AS 27
FROM tblFind_Data, tblBMS_Docs_From;

 

[PHV]

You may add a WHERE clause:

SELECT ...
FROM tblFind_Data, tblBMS_Docs_From
WHERE tblFind_Data.data IN (tblBMS_Docs_From.[01],tblBMS_Docs_From.[02]],...,tblBMS_Docs_From.[27])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bill4tektips]

PHV; Thanks a lot. Works great