Multi level groupby clause is not allowed in a subquery

  • Thread starter mark_jm via AccessMonster.com
  • Start date
M

mark_jm via AccessMonster.com

I have a data base used to transact sales . I have an "invoiced sales report",
which has a data soiurce called "invoiced sales query". It pulls invoices out
of the database. The report groupsm them by invoiced date(month). One of the
fields in the query was a calculated field:

GP: [sell price]-([cost price]/10.5)

Where 10.5 is the exchrate of my buying currency.

The report worked fine

Now the calculated field looks like this

GP: [sell price]-([cost price]/[exratepayed])

where exratepayed is selected from a seperate table of exchange rates. That
statement looks like this

exratepayed: (SELECT [currency_rates_table].[rate] FROM [currency_rates_table]
WHERE([currency type] = [Suppliers].[currency] AND ([from]<=[Invoice date]
+30) AND ([to]>[Invoice date]+30) )).

It selects a rate from a "curency_rates_table" where the currencye is = to
the suppliers default currency, and the invoiced date falls between a date in
an effective "from" and an effective "to" field plus 30 days.

Since I aded this formula the report will not run.

I get the error: Multi level groupby clause is not allowed in a subquery.

Can anyone help me understand this error, and offer a solution?
 
D

Duane Hookom

A less efficient but possibly workable solution is to change the subquery
into a DLookup():

exratepayed: Dlookup("[rate]","[currency_rates_table]",
"[currency type] = " & [Suppliers].[currency] & " AND [from]<=#" & [Invoice
date]
+30 & "# AND [to]> #" & [Invoice date]+30 & "#")

This assumes Currency Type is numeric. If it is text, try:
exratepayed: Dlookup("[rate]","[currency_rates_table]",
"[currency type] = """ & [Suppliers].[currency] & """ AND [from]<=#" &
[Invoice date]
+30 & "# AND [to]> #" & [Invoice date]+30 & "#")
 
A

Allen Browne

Duane's solution is perfect for a small table.

Here are some alternatives if the domain aggregate performance is too slow:
http://allenbrowne.com/subquery-02.html#MultiLevelGroupBy

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

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

Duane Hookom said:
A less efficient but possibly workable solution is to change the subquery
into a DLookup():

exratepayed: Dlookup("[rate]","[currency_rates_table]",
"[currency type] = " & [Suppliers].[currency] & " AND [from]<=#" &
[Invoice
date]
+30 & "# AND [to]> #" & [Invoice date]+30 & "#")

This assumes Currency Type is numeric. If it is text, try:
exratepayed: Dlookup("[rate]","[currency_rates_table]",
"[currency type] = """ & [Suppliers].[currency] & """ AND [from]<=#" &
[Invoice date]
+30 & "# AND [to]> #" & [Invoice date]+30 & "#")


--
Duane Hookom
Microsoft Access MVP


mark_jm via AccessMonster.com said:
I have a data base used to transact sales . I have an "invoiced sales
report",
which has a data soiurce called "invoiced sales query". It pulls invoices
out
of the database. The report groupsm them by invoiced date(month). One of
the
fields in the query was a calculated field:

GP: [sell price]-([cost price]/10.5)

Where 10.5 is the exchrate of my buying currency.

The report worked fine

Now the calculated field looks like this

GP: [sell price]-([cost price]/[exratepayed])

where exratepayed is selected from a seperate table of exchange rates.
That
statement looks like this

exratepayed: (SELECT [currency_rates_table].[rate] FROM
[currency_rates_table]
WHERE([currency type] = [Suppliers].[currency] AND ([from]<=[Invoice
date]
+30) AND ([to]>[Invoice date]+30) )).

It selects a rate from a "curency_rates_table" where the currencye is =
to
the suppliers default currency, and the invoiced date falls between a
date in
an effective "from" and an effective "to" field plus 30 days.

Since I aded this formula the report will not run.

I get the error: Multi level groupby clause is not allowed in a subquery.
Click to expand...
Click to expand...
 
M

mark_jm via AccessMonster.com

Top