Sub-Query

[Robert_DubYa]

I'm currently using the dsum function to sum data for a report. This runs
very slow. I've read in other posts that a sub-query will speed up the
process. I've tried to write a sub-query but I just can not get it to work.
I'm pasting in the SQL for my dsum query. If someone could tell me how to do
a sub query I would be very greatful:

SELECT [tblInvToHSB-Messages].ShortItem, Sum([tblInvToHSB-Messages].Qty) AS
[Qty Due This Week], [tblInvToHSB-Messages].Branch,
DSum("[Qty]","tblInvToHSB-Messages","[ShortItem] = " & [ShortItem] & "") AS
[Total Qty Due This Week] INTO [tblInvToHS-2Step2]
FROM [tblInvToHSB-Messages]
WHERE ((([tblInvToHSB-Messages].MsgTyp)="95") AND
(([tblInvToHSB-Messages].[Srt Date])>=Date() And ([tblInvToHSB-Messages].[Srt
Date])<=Date()+7))
GROUP BY [tblInvToHSB-Messages].ShortItem, [tblInvToHSB-Messages].Branch
HAVING ((([tblInvToHSB-Messages].ShortItem)=30962) AND
(([tblInvToHSB-Messages].Branch) In (" MVECHS"," MTSPRO","
SVECDN")));


Also, is there a way to do this in design view? I really don't use SQL that
often.

Thanks for your help,
Robert

 

[Klatuu]

Do the summing in the report. It is actually much faster that way.

 

[Duane Hookom]

You could try something like:
SELECT [tblInvToHSB-Messages].ShortItem,
Sum([tblInvToHSB-Messages].Qty) AS [Qty Due This Week],
[tblInvToHSB-Messages].Branch,
(SELECT Sum([Qty])
FROM [tblInvToHSB-Messages] A
WHERE A.[ShortItem] = [tblInvToHSB-Messages].[ShortItem]) AS [Total Qty Due
This Week]
INTO [tblInvToHS-2Step2]
FROM [tblInvToHSB-Messages]
WHERE MsgTyp="95" AND
[Srt Date] BETWEEN Date() AND Date()+7 AND
ShortItem=30962 AND
Branch In (" MVECHS"," MTSPRO"," SVECDN");
GROUP BY [tblInvToHSB-Messages].ShortItem,
[tblInvToHSB-Messages].Branch;

You may need to add the subquery into the GROUP BY. Also, it seems you want
the same sum for multiple ShortItems within the same Branch.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Do the summing in the report. It is actually much faster that way.
--
Dave Hargis, Microsoft Access MVP

I'm currently using the dsum function to sum data for a report. This runs
very slow. I've read in other posts that a sub-query will speed up the
process. I've tried to write a sub-query but I just can not get it to work.
I'm pasting in the SQL for my dsum query. If someone could tell me how to do
a sub query I would be very greatful:

SELECT [tblInvToHSB-Messages].ShortItem, Sum([tblInvToHSB-Messages].Qty) AS
[Qty Due This Week], [tblInvToHSB-Messages].Branch,
DSum("[Qty]","tblInvToHSB-Messages","[ShortItem] = " & [ShortItem] & "") AS
[Total Qty Due This Week] INTO [tblInvToHS-2Step2]
FROM [tblInvToHSB-Messages]
WHERE ((([tblInvToHSB-Messages].MsgTyp)="95") AND
(([tblInvToHSB-Messages].[Srt Date])>=Date() And ([tblInvToHSB-Messages].[Srt
Date])<=Date()+7))
GROUP BY [tblInvToHSB-Messages].ShortItem, [tblInvToHSB-Messages].Branch
HAVING ((([tblInvToHSB-Messages].ShortItem)=30962) AND
(([tblInvToHSB-Messages].Branch) In (" MVECHS"," MTSPRO","
SVECDN")));


Also, is there a way to do this in design view? I really don't use SQL that
often.

Thanks for your help,
Robert