Hello,
I have found a similar question with an answer, which I tried without success.
Let me first explain what I want to do. We are trying to use Jet to get reports from our door system to see who has come into/left the building.
We have a PC running a SQL database, and I have connected via Jet. There is a table called "EventsEx" which stores all entry/exit events and things of that nature, and a table called "UsersEx" which houses a list of all users we have set up on the system.
Now what I am trying to do, is to get a list of unique "UserID"s in the EventsEx table for a given day. I then want to compare that to the UsersEx table, to determine who has not been in. If there are no entrys in the EventsEx table for a UserID, I know they have not entered the building.
I tried using a filter on the EventsEx table to get the UserID field between two dates, and a filter to get all unique UserIDs from the UsersEx table. I then tried using an NP difference to see which IDs where not in the filter.
The solution I found on another question which didnt work was:=NL("Rows",NP("Difference",NL("Filter","UsersEx","UserID","DataSource=","Net2"),NL("Filter","EventsEx","UserID","DataSource=","Net2","EventTime","16/10/2013..17/10/2013")),,"DataSource=","Net2")
This just seems to return all IDs from the UsersEx table. I am sure what I want is possible, but I can't quite wrap my head around it :(
Appreciate any help or guidance.
Eds
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment Ok so I have no idea why this works, but I have put the All Users filter in cell C3, I then put the events for the day filter in C4, and then I did:
=NL("Rows", NP("Difference",$C3,$C4))This seems to work fine.
Not sure why it wasn't working before, but I will mark the question as solved.
Thanks Hughes.
Eds -
Jet Reports Historic Posts Hi Eds,
I think the formulas you have look like they are correct. However, I wonder about your filter for "EventTime" and whether it's really giving you what you want. I would try pulling out your second NL(Filter) and replicating it to see what it is returning like this:=NL("Rows",NL("Filter","EventsEx","UserID","DataSource=","Net2","EventTime","16/10/2013..17/10/2013"))
My guess is that this formula isn't actually returning anything, which is why your difference appears to be returning all users. Is this the case?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
When replicating that filter, it returns 77 rows.
If I replicate the all users filter, it returns 207.
This means that the filter on the EventsEx table is working correctly, and is only returning users who have came in that day.
When I do an NP Difference between the two filters, and I "Evaluate function" within the Jet function wizard, it gives me a result of "{""""}" which I assume is an empty array.
Perhaps the NP Difference only gets the difference between two filters, when they are looking at the same table?
If that's not the case, I am truly stumped :P
Thanks again for your help as always!!
Eds
Please sign in to leave a comment.