Manipulating records into one column

[neileg]

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?

That's the answer as a fraction of a day.
-0.0003587963 x 24x60x60 = 31 seconds
And it's negative because you've subracted a later time from an earlier one.

 

[paulsimo]

What can I say Jal but thanks very much, that is just amazing that you have solved that problem, I shall test it through with a whole load of records and times but from the initial results it seems to be achieving what I wanted from it.

I have tried adding the final INNERJOIN statement as you suggested into the qryFinal query, unfortunately it returns a message:-

The specified field 'qryFinal.BadgeID' could refer to more than one table listed in the FROM clause of your sql statement.

Any ideas what this is referring to?

Once again, many, many thanks for your continued help.

 

[jal]

Hm....I thought I had tested that query, and furthermore I don't really understand why the engine finds that field ambiguous. But anyway, I have now removed the ambiguity.


SELECT Q.Dup, Q.theOrdinal, Q.TheZone, Q.Time, I.* 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 Q2.BadgeID FROM qryMarkDups as Q2 WHERE Q2.TheZone LIKE "Zone1" OR Q2.IntervalinSeconds > 15)
)

I'm also wondering if I should have combined some of those queries because, if you try to enrich them any further, the engine will complain, "Query too complex."

 

[jal]

I don't how much data you will have, so I'm not sure if performance will ever matter. The third query (qryIntervals) can be made faster. Instead of a subquery, it ca be done as a self-join.

SELECT Q1.*, DateDiff('s',Q2.Time ,Q1.Time) as IntervalInSeconds
FROM qryOrdinals as Q1
LEFT JOIN qryOrdinals as Q2
ON Q1.BadgeID = Q2.BadgeID
AND Q1.TheOrdinal = Q2.TheOrdinal + 1

 

[jal]

Ok, I guess I'm done. The second query (qryOrdinals) is likewise non-performant but I don't know a way to change that, except possibly by looping through a VBA recordset. Assigning ordinals is a weakness of Access although it is a strength of Sql Server 2005.

 

[paulsimo]

Hi Jal

That is just brilliant, I do appreciate the time you have spent on this problem, I certainly would never have got there on my own, however it has made me more determined than ever to try and grasp the sql language. As far as performance goes, I shall test it with more records, I expect I will be holding about 500 records, possibly up to 1000. The result don't have to be instantaneous, one to try out over the weekend.
I added the other qryIntervals statement and that has definitely made an improvement in performance. I don't expect the performance issue to be a problem.

Thank you very much, keep up the good work, you are doing an absolute sterling job on this forum. Have a great weekend.