Using results from a Sub Query in a Crosstab Query

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
 
G

Guest

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;
 
G

Guest

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
 
G

Guest

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top