Manipulating records into one column

[paulsimo]

Could I please ask for some help with a current project I am working on.
The database, named “Zone Detect” at the moment is made up of 3 tables, these being “Items”, “Zone 1” & “Zone 2”.
Both tables “Zone 1 & Zone 2” are linked externally via a separate access database. I then use the link tables facility, through a network to input the external data into the tables “Zone 1 & Zone 2”. The detection devices used are RFID readers.

The idea is that as the items pass by the RFID reader a unique Badge ID is read and the time it was detected is logged. Zone 1 is the primary detector and Zone 2 is a back up for Zone 1.
In reality most Badge ID’s are picked up by both Zone 1 and Zone 2, however some are only picked up by one or the other of the Zone detectors. It is necessary to have both detectors for the required reliability of the project.

What I am trying to do is gather the information (detected BadgeID & Time Logged) into one column within a query. From the database I have attached you will see, I have the information from both Zone 1 & Zone 2, but in 2 separate columns.

Ideally if the query could prioritise and place all detected “Zone 1” records in a column, plus all detected “Zone 2” records (that were not detected by Zone 1), ignoring the “Zone 2” records that were also read by “Zone 1” to produce one column of times, as below.

1234 ABC FX2 Red S 9:30:46
1235 ABC FX7 Blue S 9:30:02
1236 ABC FX7 Blue M 9:30:14
1239 ABC FX3 Yellow L 9:30:18
1242 CDE HL7 Blue M 9:30:21
1241 CDE HL2 Red S 9:30:23
1237 ABC FX9 Green S 9:30:51
1250 EFG SM8 White S 9:31:28
1249 CDE HL9 Green M 9:31:59
1238 ABC FX2 Red L 9:32:22
1240 CDE HL7 Blue M 9:33:07




I am afraid this has got me baffled, I am fairly new to the Access database software and am trying to learn as I go along. I would be very grateful if somebody could suggest a way to achieve this.

 

[jal]

Try pasting the below into SQL View. However, it uses the "NOT IN" clause, though, which is terribly slow if you have large amounts of data. I'm guessing that rewriting it to avoid the NOT IN clause would be much more wordy. I hope you know SQL. Personally, I never learned how to use the query grid - I learned SQL from the getgo. I'm still a beginner though.


SELECT * FROM
(

SELECT BadgeID, Time, "Zone1" as TheZone FROM [Zone 1]
UNION
SELECT BadgeID, Time, "Zone2" as TheZone FROM [Zone 2]
WHERE BadgeID NOT IN (SELECT BadgeID FROM [Zone 1])
) as ListOfBadges
INNER JOIN Items
ON Items.BadgeID = ListOfBadges.BadgeID


Is speed going to be a big issue for you? I'm not sure I would have time to figure out how to write this query without using the slow NOT IN clause.

 

[paulsimo]

Hi Jal

Many thanks for posting a reply.
I have tried pasting the SQL into the SQL view, however when I try to open the query it replies with the following message

"Invalid bracketing of name "SELECT BadgeID, Time, Zone1 as TheZone FROM [Zone 1".

Not sure what this means, unfortunately my SQL knowledge is very limited at the moment.

As regards speed, it may be an issue depending on how slow, slow is.
I expect that the database could be dealing with 500+ records.

 

[jal]

Hi Jal

Many thanks for posting a reply.
I have tried pasting the SQL into the SQL view, however when I try to open the query it replies with the following message

"Invalid bracketing of name "SELECT BadgeID, Time, Zone1 as TheZone FROM [Zone 1".

Not sure what this means, unfortunately my SQL knowledge is very limited at the moment.

As regards speed, it may be an issue depending on how slow, slow is.
I expect that the database could be dealing with 500+ records.

Last night I downloaded your database and pasted the above in SQL view. It worked just fine. Either you are pasting wrong, or you are now using a database other than the one you provided for download. To verify, I just tried it again on the db you uploaded, and it worked fine.

Then again, I am using Access 2003. Maybe "Time" is a reserved keyword in Access 2007 in which case it needs bracketing.

 

[paulsimo]

I have checked the pasted SQL code, for some unknown reason when I copied and pasted it into SQL view, it replaced the first and last brackets, with square brackets, having changed these back to how your example shows, it now works fine.

Thanks very much, I really appreciate you're help.

Just one more thing, as I open the query, a box appears asking to enter parameter value, Zone 1, if I press enter it then asks for parameter value, Zone 2, entering again then opens up the query with the correct results.

Is it possible for the query to open straight away, without the parameter value pop up boxes. For myself, this would not be a problem, but for other users of this database it may cause problems. The database is likely to be used by a number of people and I want to make it as user friendly as possible.

 

[jal]

I have checked the pasted SQL code, for some unknown reason when I copied and pasted it into SQL view, it replaced the first and last brackets, with square brackets, having changed these back to how your example shows, it now works fine.

Thanks very much, I really appreciate you're help.

Just one more thing, as I open the query, a box appears asking to enter parameter value, Zone 1, if I press enter it then asks for parameter value, Zone 2, entering again then opens up the query with the correct results.

Is it possible for the query to open straight away, without the parameter value pop up boxes. For myself, this would not be a problem, but for other users of this database it may cause problems. The database is likely to be used by a number of people and I want to make it as user friendly as possible.

Access asks you to supply a parameter value when it hits an uncrecognized name, in this case "Zone 1". It thinks that you included a new name on purpose to serve as a variable for intaking the desired value at runtime.

I'm guessing the problem is that you named the table "Zone1" instead of "Zone 1" (in other words delete the spaces from my query above and then repaste it into sql view).

 

[neileg]

The quote marks shouldn't be in the SQL.

 

[jal]

Wait a minute - now I'm confused, though, because I ran the query in your database and didn't have this problem.

 

[jal]

Thanks Neilig, although I wonder why it didn't throw any parameter boxes for me.

Neilig's always right, it seems, but if you still have a problem, maybe try single quotes instead of the double quote char.

 

[jal]

And by the way, 500 records shouldn't put too much of a burden on the NOT IN clause.

 

[jal]

Bottom line is, I tried it again and didn't have a problem. You said before that your pasting isn't working perfectly. I think that's still the problem, but I'm not exactly sure which part of the query is pasting incorrectly in your case.

I seem to distinctly recall having pasting problems with Access 2000 but not with 2003.

 

[paulsimo]

Okay, everything is sorted and working brilliantly.
When checking the SQL code I realised that the tables Zone 1 & Zone 2 only have 2 fields each, namely BadgeID and Time, so I simply omitted the extra non existant fields (Zone1 & Zone2) from the code, and hey presto it works.

Many many thanks for all the input and solutions offered and with such promptness, it really is appreciated.

Keep up the good work, I may need to call on you again sometime in the future. I really must try and get to grips with SQL.

All the best
paulsimo

 

[paulsimo]

I would really like a little help with an amendment to an SQL Query I have.

Jal very kindly sorted out this Query for me, which produces one list of recorded times from 2 tables.

However, using this method, I have encountered a problem, if the objects were to travel around a circuit and be read by the same detectors for a 2nd or 3rd time, problems can then occur.
Imagine the scenario, an object passes by the detectors for the first time and is detected by Zone2 only, this is the back up and would be added to the query, if now the same object travels around a circuit and passes by the detectors for a 2nd time, on this occasion both Zone1 and Zone2 detects the object the detected time from Zone1 would now replace the first (1st circuit) Zone2 time.
What I now need is for the query to detect both times if the time difference is greater than 15 seconds but prioritise (Zone1) if the time difference is less than15 seconds. In the scenario above this would list the Zone2 time (1st circuit) and Zone1 time (2nd circuit).

The detectors for Zone1 and Zone2 are placed close together, the object passing by would only take 2 to 3 seconds to pass both (the 15 seconds is just a safety margin), however if the object was to do a full circuit to return to the detectors a 2nd time this would take at least 5 minutes.

I thought of adding a NOT BETWEEN statement into the query, stating :-
If Zone2, Time NOT BETWEEN Zone1, Time AND Zone1, Time + 15 seconds then include Zone2 Time to the query. (I know this is not the correct syntax, but hopefully it explains what I want it to achieve).

Unfortunately I cannot seem to be able to get the required results. Is it possible to add this into the initial Query.

The database,attached, named “Zone Detect” at the moment is made up of 3 tables, these being “Items”, “Zone 1” & “Zone 2”.
Both tables “Zone 1 & Zone 2” are linked externally via a separate access database. I then use the link tables facility, through a network to input the external data into the tables “Zone 1 & Zone 2”. The detection devices used are RFID readers.

The idea is that as the items pass by the RFID reader a unique Badge ID is read and the time it was detected is logged. Zone 1 is the primary detector and Zone 2 is a back up for Zone 1.
In reality most Badge ID’s are picked up by both Zone 1 and Zone 2, however some are only picked up by one or the other of the Zone detectors. It is necessary to have both detectors for the required reliability of the project.

 

[jal]

I'm taking a look at this - one thing I'm inexperienced here with is how timespans work in Access. I thought it was giving me the result in seconds. Yet when I subtract the following times:

SELECT #9:30:14 AM# - #9:30:45 AM#

(I thought I would get about 31 seconds) I get a result of -0.0003587963

What's that?

 

[jal]

Ok, I need to use the DateDiff function - got it.

 

[jal]

Can we assume that every Zone1 scan is a new, valid entry - that it's not a dup?

 

[jal]

In other words, can Zone2 scan first, and then 2 seconds later Zone1 scans?

That's not possible, right?

 

[jal]

God am I ever having a hard time figuring out what you want. All this is confusing to me about 1st circuit and 2nd circuit. I've gotta go be back later hopefully.

 

[jal]

Allright, see my solution attached. Needs a lot of testing.

It has several queries, but you only need to run the last one (qryFinal) because they are cumulative.

 

[jal]

I forgot to INNER JOIN the final result with the Items table. Change that qryFinal to this code:


SELECT * FROM qryMarkDups as Q
INNER JOIN Items as I ON I.BadgeID = Q.BadgeID
WHERE (TheZone LIKE "Zone1" OR IntervalinSeconds > 15)
OR
(
TheZone LIKE "Zone2"
AND DUP = FALSE
AND Q.BadgeID NOT IN (SELECT BadgeID FROM qryMarkDups WHERE TheZone LIKE "Zone1" OR IntervalinSeconds > 15)
)