Solved

How to Simulate an Unmatched Query in a Microsoft Access 2013 Web App

Posted on 2014-11-13
12
452 Views
Last Modified: 2014-11-13
I have a situation in one of Microsoft Access 2013 Web Applications in which I need to simulate an unmatched query (to return the records from table A that do not exist in table B). Since this can't actually be done with a query on this platform I was wondering if anyone has a solution to do it using ForEachRecord loops in a data macro.  Thanks for your help!
0
Comment
Question by:penlandt
  • 7
  • 5
12 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Are you saying you cannot create an Outer Join with two tables to do this in the Web App side of A2013 ?
0
 
LVL 1

Author Comment

by:penlandt
Comment Utility
Thanks for your quick reply.  As far as I know outer joins are not supported in Access 2013 Web Apps (though they are of course supported in desktop apps).  Additionally, it is my understanding that nested SELECT statements are not supported in Web Apps.  This leaves me unable to figure out how to perform an unmatched query in any way other than to loop through the tables and identify which rows exist in table A that do not exist in table B.  I know that I could then insert the resulting records into a temporary table and use that as the result of my simulated unmatched query, however I'm wondering if there's a more elegant way to accomplish this within the constraints of the Web App platform.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Let me try it when I get home tonight ...

But ... the image below is a query from Jeff Conrad's Access 2013 Inside Out book ... and his BOSS app, which I have (.accdw) on this system. So ....


outer join
0
 
LVL 1

Author Comment

by:penlandt
Comment Utility
Thanks again.  That example looks like an inner join to me though.  Am I missing something in the example?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
OOP ... gezze ...  trust me, it's been a bad hair day ALL day ....
standby
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
OK ... I was able to create an Other Join, save and run the query.

1
2
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:penlandt
Comment Utility
I appreciate the help!  If I'm reading that query correctly, it is including all of the records in tblSchedule - including records that exist in tblEmployees.  That's not exactly what I'm looking for.  Using your example I would want to return only the records in tblSchedule that have NO MATCHING RECORD in tblEmployees (joined on the EmployeeID of course).  Unfortunately this query won't do that.

I am relatively certain that the type of procedure I'm trying to do cannot be done via a query in a Web Application.  My understanding is that it can only be done via a data macro using a ForEachRecord loop on TableA and then a LookupRecord on TableB to find out whether there it contains a matching record, retaining the record from TableA if there is no matching record in TableB.  That's easy enough to do however I don't know how/where to store the results other than to put them in in a Temporary table.  As I said, that's not a very elegant way to do this but it will work.  I was hoping someone here had done this before and had worked out a better way.  Thanks again.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
Comment Utility
I just showed you can create an Outer Join - which is what you need. Except ... in the Join Properties screen shot, I forgot to select # 2 before I did the screenshot. So, Outer Joins are supported.

if you were to add a criteria of Is Null for the EmployeeIDFK in tblSchedule, then that would do ... for this example - what you are looking for.  

"to return the records from table A that do not exist in table B"
A=Table Employees and B=Table Schedule - in my screenshot example.
0
 
LVL 1

Author Closing Comment

by:penlandt
Comment Utility
I see - part of the problem was that I can't see the criteria in your screenshots so I wasn't aware that you were entering anything there.  I tried it as you described and it does work.  Thank you very much for your help.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
oops again, that screenshot was cut off ... sorry about that.
Good ... you got it !

I HIGHLY recommend Jeff Conrad's book Access 2013 Inside Out (Amazon). You can download several ready to use  Web Apps ... and see how things are done. VERY cool.

mx
0
 
LVL 1

Author Comment

by:penlandt
Comment Utility
Thanks again.  I'll definitely get it.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I have personally met and talked with Jeff several times at the MVP Summit in Redmond (last week in fact) and his passion for Access and Web Apps is second to none.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

6 Experts available now in Live!

Get 1:1 Help Now