Access2000 query (with subquery)

[Arvi Laanemets]

Hi


I have a table tblTransactions: TransactID, TransactDate, DeviceID, Tabn,
....
The table determines the user (TabN) for any registered item (DeviceID) at
any time starting from date, the item was registered as used by some user.

Fields:
TransactID - an autonumeric field, main key;
TransactDate - a date field;
DeviceID - an indexed text field, which determines a device from table
tblDevices. Is used to set relation betveen tables tblDevices >
tblTransactions.
TransactDate+DeviceID form a compound index in table tblTransactions - every
device can have only one transaction on single day;
TabN - an indexed text field, which may contains numeric or alphanumeric
strings, like "9999" or "CCC9999", and which determines the user, the device
was assigned at date TransactDate to. Is used to set relation between tables
tblTransactions > tblUsers. (Users with numeric Tabn determine our
employees, and all info about them (dates of engagement/disengagement, their
movements between departments, etc., are imported and refreshed from our
staff management program. Users with alphanumeric Tabn determine various
group users, or persons which do use our devices, but aren't our employees,
and they are managed in Access application locally.)

I need a query (as source for a report), where for any date [QueryDate]
determined by user, are returned from table tblTransactions for every
DeviceID with any TransactDate <=[QueryDate] the row with latest
TransactDate. I.e. I want to know who used any device registered at this
date, and/or which devices were assigned for any user at this date.

I tried to get wanted result in 2 steps.

qUserDevices0 (I saved parameter qDate, because I'll need it to compose
report heading):
SELECT CDate([qDate]) AS QueryDate, a.DeviceID, Max(a.TransactDate) AS
LastTransactDate
FROM tblTransactions AS a
WHERE (((a.TransactDate)<=[qDate] And (a.TransactDate) Is Not Null))
GROUP BY [qDate], a.DeviceID
ORDER BY a.DeviceID;

qUserDevices:
SELECT a.QueryDate, a.DeviceID, a.LastTransactDate AS TransactDate, b.TabN
FROM qUserDevices0 AS a, qUserDevices1 AS b
WHERE b.DeviceID=a.DeviceID And b.TransactDate=a.LastTransactDate;


The query qUserDevices returns no records!

When I save qUserDevices0 as a table, and use this table as one source for
qUserDevices instead of qUserDevices0, then it works OK. But this will be
too clumsy a solution - especially when to consider that reveral users may
want to run this report at same time and with different [qDate]'s of-course.
When I remove the field b.Tabn from query qUserDevices, then it works OK
again. But I don't get any needed information.
My first thougth was, that the problem originates from having numeric and
alphanumeric strings in field TabN. I tried to ose similar queries to get
TransactID's instead of TabN's - but got again empty result table.

Can someone explain, why behave MS queries in such a way, and what would be
a solution?
Thanks in advance!

 

[Allen Browne]

Hi Arvi

Your requirements here look very similar to Michel Walsh's examples in this
article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

He explains 4 ways to achieve this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table tblTransactions: TransactID, TransactDate, DeviceID, Tabn,
...
The table determines the user (TabN) for any registered item (DeviceID) at
any time starting from date, the item was registered as used by some user.

Fields:
TransactID - an autonumeric field, main key;
TransactDate - a date field;
DeviceID - an indexed text field, which determines a device from table
tblDevices. Is used to set relation betveen tables tblDevices >
tblTransactions.
TransactDate+DeviceID form a compound index in table tblTransactions -
every device can have only one transaction on single day;
TabN - an indexed text field, which may contains numeric or alphanumeric
strings, like "9999" or "CCC9999", and which determines the user, the
device was assigned at date TransactDate to. Is used to set relation
between tables tblTransactions > tblUsers. (Users with numeric Tabn
determine our employees, and all info about them (dates of
engagement/disengagement, their movements between departments, etc., are
imported and refreshed from our staff management program. Users with
alphanumeric Tabn determine various group users, or persons which do use
our devices, but aren't our employees, and they are managed in Access
application locally.)

I need a query (as source for a report), where for any date [QueryDate]
determined by user, are returned from table tblTransactions for every
DeviceID with any TransactDate <=[QueryDate] the row with latest
TransactDate. I.e. I want to know who used any device registered at this
date, and/or which devices were assigned for any user at this date.

I tried to get wanted result in 2 steps.

qUserDevices0 (I saved parameter qDate, because I'll need it to compose
report heading):
SELECT CDate([qDate]) AS QueryDate, a.DeviceID, Max(a.TransactDate) AS
LastTransactDate
FROM tblTransactions AS a
WHERE (((a.TransactDate)<=[qDate] And (a.TransactDate) Is Not Null))
GROUP BY [qDate], a.DeviceID
ORDER BY a.DeviceID;

qUserDevices:
SELECT a.QueryDate, a.DeviceID, a.LastTransactDate AS TransactDate, b.TabN
FROM qUserDevices0 AS a, qUserDevices1 AS b
WHERE b.DeviceID=a.DeviceID And b.TransactDate=a.LastTransactDate;


The query qUserDevices returns no records!

When I save qUserDevices0 as a table, and use this table as one source for
qUserDevices instead of qUserDevices0, then it works OK. But this will be
too clumsy a solution - especially when to consider that reveral users may
want to run this report at same time and with different [qDate]'s
of-course.
When I remove the field b.Tabn from query qUserDevices, then it works OK
again. But I don't get any needed information.
My first thougth was, that the problem originates from having numeric and
alphanumeric strings in field TabN. I tried to ose similar queries to get
TransactID's instead of TabN's - but got again empty result table.

Can someone explain, why behave MS queries in such a way, and what would
be a solution?
Thanks in advance!

 

[Arvi Laanemets]

Thanks! I tried the 1st of them and it worked.

But I'm still qonfused - Allen's solution is practically same I myself
used - and first-level query returns exactly same result as my saved query
did (as I waited, of-course), but why did second-level query work with RIGHT
JOIN ... , and didn't work with WHERE ... syntax (I'm more comfortable with
older syntax from ages I worked with FoxPro, and so long it never
dissappointed me) ???


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Hi Arvi

Your requirements here look very similar to Michel Walsh's examples in
this article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

He explains 4 ways to achieve this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table tblTransactions: TransactID, TransactDate, DeviceID, Tabn,
...
The table determines the user (TabN) for any registered item (DeviceID)
at any time starting from date, the item was registered as used by some
user.

Fields:
TransactID - an autonumeric field, main key;
TransactDate - a date field;
DeviceID - an indexed text field, which determines a device from table
tblDevices. Is used to set relation betveen tables tblDevices >
tblTransactions.
TransactDate+DeviceID form a compound index in table tblTransactions -
every device can have only one transaction on single day;
TabN - an indexed text field, which may contains numeric or alphanumeric
strings, like "9999" or "CCC9999", and which determines the user, the
device was assigned at date TransactDate to. Is used to set relation
between tables tblTransactions > tblUsers. (Users with numeric Tabn
determine our employees, and all info about them (dates of
engagement/disengagement, their movements between departments, etc., are
imported and refreshed from our staff management program. Users with
alphanumeric Tabn determine various group users, or persons which do use
our devices, but aren't our employees, and they are managed in Access
application locally.)

I need a query (as source for a report), where for any date [QueryDate]
determined by user, are returned from table tblTransactions for every
DeviceID with any TransactDate <=[QueryDate] the row with latest
TransactDate. I.e. I want to know who used any device registered at this
date, and/or which devices were assigned for any user at this date.

I tried to get wanted result in 2 steps.

qUserDevices0 (I saved parameter qDate, because I'll need it to compose
report heading):
SELECT CDate([qDate]) AS QueryDate, a.DeviceID, Max(a.TransactDate) AS
LastTransactDate
FROM tblTransactions AS a
WHERE (((a.TransactDate)<=[qDate] And (a.TransactDate) Is Not Null))
GROUP BY [qDate], a.DeviceID
ORDER BY a.DeviceID;

qUserDevices:
SELECT a.QueryDate, a.DeviceID, a.LastTransactDate AS TransactDate,
b.TabN
FROM qUserDevices0 AS a, qUserDevices1 AS b
WHERE b.DeviceID=a.DeviceID And b.TransactDate=a.LastTransactDate;


The query qUserDevices returns no records!

When I save qUserDevices0 as a table, and use this table as one source
for qUserDevices instead of qUserDevices0, then it works OK. But this
will be too clumsy a solution - especially when to consider that reveral
users may want to run this report at same time and with different
[qDate]'s of-course.
When I remove the field b.Tabn from query qUserDevices, then it works OK
again. But I don't get any needed information.
My first thougth was, that the problem originates from having numeric and
alphanumeric strings in field TabN. I tried to ose similar queries to get
TransactID's instead of TabN's - but got again empty result table.

Can someone explain, why behave MS queries in such a way, and what would
be a solution?
Thanks in advance!

 

[Allen Browne]

Would the clause:
WHERE b.DeviceID=a.DeviceID And b.TransactDate=a.LastTransactDate
be equivalent to an INNER JOIN rather than a RIGHT JOIN?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks! I tried the 1st of them and it worked.

But I'm still qonfused - Allen's solution is practically same I myself
used - and first-level query returns exactly same result as my saved query
did (as I waited, of-course), but why did second-level query work with
RIGHT JOIN ... , and didn't work with WHERE ... syntax (I'm more
comfortable with older syntax from ages I worked with FoxPro, and so long
it never dissappointed me) ???


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Hi Arvi

Your requirements here look very similar to Michel Walsh's examples in
this article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

He explains 4 ways to achieve this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table tblTransactions: TransactID, TransactDate, DeviceID,
Tabn, ...
The table determines the user (TabN) for any registered item (DeviceID)
at any time starting from date, the item was registered as used by some
user.

Fields:
TransactID - an autonumeric field, main key;
TransactDate - a date field;
DeviceID - an indexed text field, which determines a device from table
tblDevices. Is used to set relation betveen tables tblDevices >
tblTransactions.
TransactDate+DeviceID form a compound index in table tblTransactions -
every device can have only one transaction on single day;
TabN - an indexed text field, which may contains numeric or alphanumeric
strings, like "9999" or "CCC9999", and which determines the user, the
device was assigned at date TransactDate to. Is used to set relation
between tables tblTransactions > tblUsers. (Users with numeric Tabn
determine our employees, and all info about them (dates of
engagement/disengagement, their movements between departments, etc., are
imported and refreshed from our staff management program. Users with
alphanumeric Tabn determine various group users, or persons which do use
our devices, but aren't our employees, and they are managed in Access
application locally.)

I need a query (as source for a report), where for any date [QueryDate]
determined by user, are returned from table tblTransactions for every
DeviceID with any TransactDate <=[QueryDate] the row with latest
TransactDate. I.e. I want to know who used any device registered at this
date, and/or which devices were assigned for any user at this date.

I tried to get wanted result in 2 steps.

qUserDevices0 (I saved parameter qDate, because I'll need it to compose
report heading):
SELECT CDate([qDate]) AS QueryDate, a.DeviceID, Max(a.TransactDate) AS
LastTransactDate
FROM tblTransactions AS a
WHERE (((a.TransactDate)<=[qDate] And (a.TransactDate) Is Not Null))
GROUP BY [qDate], a.DeviceID
ORDER BY a.DeviceID;

qUserDevices:
SELECT a.QueryDate, a.DeviceID, a.LastTransactDate AS TransactDate,
b.TabN
FROM qUserDevices0 AS a, qUserDevices1 AS b
WHERE b.DeviceID=a.DeviceID And b.TransactDate=a.LastTransactDate;


The query qUserDevices returns no records!

When I save qUserDevices0 as a table, and use this table as one source
for qUserDevices instead of qUserDevices0, then it works OK. But this
will be too clumsy a solution - especially when to consider that reveral
users may want to run this report at same time and with different
[qDate]'s of-course.
When I remove the field b.Tabn from query qUserDevices, then it works OK
again. But I don't get any needed information.
My first thougth was, that the problem originates from having numeric
and alphanumeric strings in field TabN. I tried to ose similar queries
to get TransactID's instead of TabN's - but got again empty result
table.

Can someone explain, why behave MS queries in such a way, and what would
be a solution?
Thanks in advance!