Solved

Access 97: DLookup Query Criteria Question

Posted on 2003-11-06
6
424 Views
Last Modified: 2007-12-19
To keep it short: I have a Query where I am trying to set up DLookup criteria based on the following tables and headers (simplified):

table 1: ID, topic, location, team
table 2: ID, topic, location
table 3: team, topic, location

Basically in my criteria I want to return the following:
if the ID in table 1 = the ID in table 2 return the topic from table 2; if not, but if team in table 1 = team in table 3, return the topic in table 3

I'll need to do something similar for location, but I should be able to figure it out based on the response.

Thanks in advance
0
Comment
Question by:TheJMan
  • 3
  • 2
6 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9694974
Hey TheJMan!

  Are you sure you don't want to just link in the appropriate tables??
  Djunk() functions are slow.  And compound that with using them as an expression in a column, where they have to run ONCE FOR EACH RECORD, slower.  Compound that with creating three such expression columns, SLOWEST!

  I would recommend that you use JOINs in the query to achieve this.

regards
Jack
0
 
LVL 18

Assisted Solution

by:bonjour-aut
bonjour-aut earned 250 total points
ID: 9694995
3 little qeries:

qry1 - does 1st condition

SELECT tab1.ID, tab2.topic
FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID;

qry2 -  does 2nd, where 1st is null

SELECT tab1.ID, tab3.topic
FROM (tab1 INNER JOIN tab3 ON tab1.team = tab3.team) LEFT JOIN tab2 ON tab1.ID = tab2.ID
WHERE (((tab2.ID) Is Null));

qryfinal - does a UNION of above

SELECT * FROM qry1
UNION SELECT * FROM qry2;

could be also done in one with subqueries, but seperate version will also run on A97

regards, Franz
0
 

Author Comment

by:TheJMan
ID: 9695262
Thanks for the reply's.   Franz, I am pretty weak with SQL statements.  For your query one example where table 1 = "master table" and table 2 = "Learning Fair Presenter Lookup" I wrote the following in the criteria box under field = ID and table = master table in my (select) query:

=INNER JOIN [Learning Fair Presenter Lookup] ON [master table].[ID]=[Learning Fair Presenter Lookup].[ID]

As you are probably guessing, it didn't work. Is it a formatting issue or am I way off base?

I may follow up again on the left join and union queries b/c I've never constructed SQL statement queries before

Thanks again
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:TheJMan
ID: 9695382
Franz, I think I'm getting closer on query 1 (see below): but I still get a syntax error

SELECT master table.ID, Learning Fair Presenter Lookup.ID
FROM master table INNER JOIN Learning Fair Presenter Lookup
ON master table.ID = Learning Fair Presenter Lookup.ID
0
 

Author Comment

by:TheJMan
ID: 9695408
Got it working on number 2
0
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 250 total points
ID: 9695523
so do you need further support on that ?

if it helps, download this little example :

http://www.tplus.at/~sissi-franz/test/topics.mdb

the UNION query cannot be looked at in design mode, SQL only

Regards, Franz
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now