We help IT Professionals succeed at work.

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

penlandt
penlandt asked
on
688 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!
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Are you saying you cannot create an Outer Join with two tables to do this in the Web App side of A2013 ?

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
Thanks again.  That example looks like an inner join to me though.  Am I missing something in the example?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
OOP ... gezze ...  trust me, it's been a bad hair day ALL day ....
standby
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
OK ... I was able to create an Other Join, save and run the query.

1
2

Author

Commented:
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.
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
Thanks again.  I'll definitely get it.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.