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