Using results from a Sub Query in a Crosstab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

This has been driving me mad! I've managed to pare it down to the bare
details, and hopefully someone will have an solution to my problem:

I have a table (tbl_Prices):
id: AutoNumber
Start: DateTime
End: DateTime
Price: Currency

Start and End define a non-overlapping time-period during which Price is
charged.
For example:
1, 1/4/2004, 31/3/2005, £10.00
2, 1/4/2005, 31/3/2006, £15.00
3, 1/4/2006, 31/3/2007, $20.00

I then have a table (tbl_Sessions):

id: AutoNumber
parent_ID: number
Session: number
Date: DateTime
Exempt: Yes/No

Each Parent_ID has several sessions, each session has a session number, a
date and an exemption.

For example:
1, 1, 1, 01/06/2004, 0
2, 1, 2, 08/09/2004, 0
3, 1, 3, 16/12/2004, -1
4, 2, 1, 05/09/2005, 0
5, 2, 2, 12/09/2005, -1
6, 2, 3, 19/09/2005, -1
7, 3, 1, 23/02/2006, 0
8, 3, 2, 02/03/2006, 0
9, 3, 3, 09/03/2006, 0

What I want to do is produce a table of Parent_IDs with the price of each
session.

To test that I could pick up the price I created this query:
SELECT tbl_Sessions.id, IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices]
WHERE [Start]<[Date] AND [End] > [Date])) AS Fee
FROM tbl_Sessions;

Which worked fine.

My first attempt to create a table was:

TRANSFORM IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices] WHERE
[Start]<[Date] AND [End] > [Date])) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

This gave an error that [Date] was not recognised. So I added that as a
parameter:

PARAMETERS [Date] DateTime;
TRANSFORM IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices] WHERE
[Start]<[Date] AND [End] > [Date])) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

This then gave an error:

Multi-level GROUP By clause is not allowed in a subquery.

Is there something I've missed, a better way of handling the data or is this
impossible to do?

Thanks in advance,
Adrian
 
Subqueries and crosstabs don't play well together. You should be able to
substitute a poorly performing DLookup() for your subquery.

Try something like this as a kludgy work around:

SELECT tbl_Sessions.id,
IIf([Exempt],0,DLookup("[Price]","[tbl_Prices]","[Start]<#" & [Date] & "# AND
[End] > #" & [Date] & "#") ) AS Fee
FROM tbl_Sessions;
 
Thanks, Duane, I'm beginning to realise what problems subqueries crosstabs
cause.

Your solution almost works. In fact, were I on the left-hand side of the
Atlantic, I'm sure it would work perfectly. Unfortunately, Dlookup doesn't
appear to play nicely with the European date format.

I'll have a play around with it tomorrow, see if there's anything I can do
to force it into the correct format. Or I may just give up and write some VB
code to do the look-ups instead.

Thanks again,

Adrian

Duane Hookom said:
Subqueries and crosstabs don't play well together. You should be able to
substitute a poorly performing DLookup() for your subquery.

Try something like this as a kludgy work around:

SELECT tbl_Sessions.id,
IIf([Exempt],0,DLookup("[Price]","[tbl_Prices]","[Start]<#" & [Date] & "# AND
[End] > #" & [Date] & "#") ) AS Fee
FROM tbl_Sessions;


--
Duane Hookom
Microsoft Access MVP


Adrian Jones said:
Hi,

This has been driving me mad! I've managed to pare it down to the bare
details, and hopefully someone will have an solution to my problem:

I have a table (tbl_Prices):
id: AutoNumber
Start: DateTime
End: DateTime
Price: Currency

Start and End define a non-overlapping time-period during which Price is
charged.
For example:
1, 1/4/2004, 31/3/2005, £10.00
2, 1/4/2005, 31/3/2006, £15.00
3, 1/4/2006, 31/3/2007, $20.00

I then have a table (tbl_Sessions):

id: AutoNumber
parent_ID: number
Session: number
Date: DateTime
Exempt: Yes/No

Each Parent_ID has several sessions, each session has a session number, a
date and an exemption.

For example:
1, 1, 1, 01/06/2004, 0
2, 1, 2, 08/09/2004, 0
3, 1, 3, 16/12/2004, -1
4, 2, 1, 05/09/2005, 0
5, 2, 2, 12/09/2005, -1
6, 2, 3, 19/09/2005, -1
7, 3, 1, 23/02/2006, 0
8, 3, 2, 02/03/2006, 0
9, 3, 3, 09/03/2006, 0

What I want to do is produce a table of Parent_IDs with the price of each
session.

To test that I could pick up the price I created this query:
SELECT tbl_Sessions.id, IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices]
WHERE [Start]<[Date] AND [End] > [Date])) AS Fee
FROM tbl_Sessions;

Which worked fine.

My first attempt to create a table was:

TRANSFORM IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices] WHERE
[Start]<[Date] AND [End] > [Date])) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

This gave an error that [Date] was not recognised. So I added that as a
parameter:

PARAMETERS [Date] DateTime;
TRANSFORM IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices] WHERE
[Start]<[Date] AND [End] > [Date])) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

This then gave an error:

Multi-level GROUP By clause is not allowed in a subquery.

Is there something I've missed, a better way of handling the data or is this
impossible to do?

Thanks in advance,
Adrian
Click to expand...
Click to expand...
 
You should be able to use the Format() function inside the DLookup() to
change the dates to mm/dd/yyyy. Allen Browne has some information on this at
http://www.allenbrowne.com/ser-36.html.
--
Duane Hookom
Microsoft Access MVP


Adrian Jones said:
Thanks, Duane, I'm beginning to realise what problems subqueries crosstabs
cause.

Your solution almost works. In fact, were I on the left-hand side of the
Atlantic, I'm sure it would work perfectly. Unfortunately, Dlookup doesn't
appear to play nicely with the European date format.

I'll have a play around with it tomorrow, see if there's anything I can do
to force it into the correct format. Or I may just give up and write some VB
code to do the look-ups instead.

Thanks again,

Adrian

Duane Hookom said:
Subqueries and crosstabs don't play well together. You should be able to
substitute a poorly performing DLookup() for your subquery.

Try something like this as a kludgy work around:

SELECT tbl_Sessions.id,
IIf([Exempt],0,DLookup("[Price]","[tbl_Prices]","[Start]<#" & [Date] & "# AND
[End] > #" & [Date] & "#") ) AS Fee
FROM tbl_Sessions;


--
Duane Hookom
Microsoft Access MVP


Adrian Jones said:
Hi,

This has been driving me mad! I've managed to pare it down to the bare
details, and hopefully someone will have an solution to my problem:

I have a table (tbl_Prices):
id: AutoNumber
Start: DateTime
End: DateTime
Price: Currency

Start and End define a non-overlapping time-period during which Price is
charged.
For example:
1, 1/4/2004, 31/3/2005, £10.00
2, 1/4/2005, 31/3/2006, £15.00
3, 1/4/2006, 31/3/2007, $20.00

I then have a table (tbl_Sessions):

id: AutoNumber
parent_ID: number
Session: number
Date: DateTime
Exempt: Yes/No

Each Parent_ID has several sessions, each session has a session number, a
date and an exemption.

For example:
1, 1, 1, 01/06/2004, 0
2, 1, 2, 08/09/2004, 0
3, 1, 3, 16/12/2004, -1
4, 2, 1, 05/09/2005, 0
5, 2, 2, 12/09/2005, -1
6, 2, 3, 19/09/2005, -1
7, 3, 1, 23/02/2006, 0
8, 3, 2, 02/03/2006, 0
9, 3, 3, 09/03/2006, 0

What I want to do is produce a table of Parent_IDs with the price of each
session.

To test that I could pick up the price I created this query:
SELECT tbl_Sessions.id, IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices]
WHERE [Start]<[Date] AND [End] > [Date])) AS Fee
FROM tbl_Sessions;

Which worked fine.

My first attempt to create a table was:

TRANSFORM IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices] WHERE
[Start]<[Date] AND [End] > [Date])) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

This gave an error that [Date] was not recognised. So I added that as a
parameter:

PARAMETERS [Date] DateTime;
TRANSFORM IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices] WHERE
[Start]<[Date] AND [End] > [Date])) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

This then gave an error:

Multi-level GROUP By clause is not allowed in a subquery.

Is there something I've missed, a better way of handling the data or is this
impossible to do?

Thanks in advance,
Adrian
Click to expand...
Click to expand...
Click to expand...
 
Thanks again.

I thought of DateFormat() as soon as I got home. :/ The new query is:

TRANSFORM Min(IIf([Exempt],0,DLookUp("[Price]","[tbl_Prices]","[Start]<= " &
Format([Date],"\#mm/dd/yyyy\#") & " AND [End] >= " &
Format([Date],"\#mm/dd/yyyy\#")))) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

Again, thanks for all your help.

Adrian

Duane Hookom said:
You should be able to use the Format() function inside the DLookup() to
change the dates to mm/dd/yyyy. Allen Browne has some information on this at
http://www.allenbrowne.com/ser-36.html.
--
Duane Hookom
Microsoft Access MVP


Adrian Jones said:
Thanks, Duane, I'm beginning to realise what problems subqueries crosstabs
cause.

Your solution almost works. In fact, were I on the left-hand side of the
Atlantic, I'm sure it would work perfectly. Unfortunately, Dlookup doesn't
appear to play nicely with the European date format.

I'll have a play around with it tomorrow, see if there's anything I can do
to force it into the correct format. Or I may just give up and write some VB
code to do the look-ups instead.

Thanks again,

Adrian

Duane Hookom said:
Subqueries and crosstabs don't play well together. You should be able to
substitute a poorly performing DLookup() for your subquery.

Try something like this as a kludgy work around:

SELECT tbl_Sessions.id,
IIf([Exempt],0,DLookup("[Price]","[tbl_Prices]","[Start]<#" & [Date] & "# AND
[End] > #" & [Date] & "#") ) AS Fee
FROM tbl_Sessions;


--
Duane Hookom
Microsoft Access MVP


:

Hi,

This has been driving me mad! I've managed to pare it down to the bare
details, and hopefully someone will have an solution to my problem:

I have a table (tbl_Prices):
id: AutoNumber
Start: DateTime
End: DateTime
Price: Currency

Start and End define a non-overlapping time-period during which Price is
charged.
For example:
1, 1/4/2004, 31/3/2005, £10.00
2, 1/4/2005, 31/3/2006, £15.00
3, 1/4/2006, 31/3/2007, $20.00

I then have a table (tbl_Sessions):

id: AutoNumber
parent_ID: number
Session: number
Date: DateTime
Exempt: Yes/No

Each Parent_ID has several sessions, each session has a session number, a
date and an exemption.

For example:
1, 1, 1, 01/06/2004, 0
2, 1, 2, 08/09/2004, 0
3, 1, 3, 16/12/2004, -1
4, 2, 1, 05/09/2005, 0
5, 2, 2, 12/09/2005, -1
6, 2, 3, 19/09/2005, -1
7, 3, 1, 23/02/2006, 0
8, 3, 2, 02/03/2006, 0
9, 3, 3, 09/03/2006, 0

What I want to do is produce a table of Parent_IDs with the price of each
session.

To test that I could pick up the price I created this query:
SELECT tbl_Sessions.id, IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices]
WHERE [Start]<[Date] AND [End] > [Date])) AS Fee
FROM tbl_Sessions;

Which worked fine.

My first attempt to create a table was:

TRANSFORM IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices] WHERE
[Start]<[Date] AND [End] > [Date])) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

This gave an error that [Date] was not recognised. So I added that as a
parameter:

PARAMETERS [Date] DateTime;
TRANSFORM IIf([Exempt],0,(SELECT [Price] FROM [tbl_Prices] WHERE
[Start]<[Date] AND [End] > [Date])) AS Fee
SELECT tbl_Sessions.parent_ID
FROM tbl_Sessions
GROUP BY tbl_Sessions.parent_ID
PIVOT tbl_Sessions.Session;

This then gave an error:

Multi-level GROUP By clause is not allowed in a subquery.

Is there something I've missed, a better way of handling the data or is this
impossible to do?

Thanks in advance,
Adrian
Click to expand...
Click to expand...
Click to expand...
 
Back
Top