query construction

[klp via AccessMonster.com]

I have a query, where it will ask for the current year and the previous year
dates. Once this is processed it will produce my query as follows:

CurDate: is the month of Sept
PrevDate: is the month of Aug

Rep Date Cur Qty CurAmt PrevQty PrevAmt
1 08/01/07 12
13
1 09/08/07 5 10

1 08/15/07 1
3
2 09/01/07 2 50
2 08/02/07 25
100
2 09/19/07 1 5
4 08/31/07 15
30
4 09/01/07 6 5
5 09/06/07 45 52
5 08/28/07 10
10
7 09/13/07 43 12
7 08/22/07 2
32

What I want is to see my data on my report as follows: Summing them by Rep

Rep Cur Qty Cur Amt PrevQty PrevAmt
1 5 10 13 16

2 3 55 25 100
4 6 5 15 30
5 45 52 10 10
7 43 12 2 32


So it will group them by rep and sum the amounts up by rep. Make sense? I
wasn't sure if I need to create a crosstab query or if I can get around not
doing that. Any questions please let me know.

Thanks in advanced.

Kim P

 

[Allen Browne]

See:
Subquery basics: Year to date
at:
http://allenbrowne.com/subquery-01.html#YTD

The article explains what a subquery is, and how to use one to get the value
for another period. The example shows the year-to-date, but the principle is
the same for getting the previous month.

 

[klp via AccessMonster.com]

Having never done a subquery before, I will be excited to try this! Now, my
report is being based off of criteria(dates Start & End) entered on a screen.
Should I depict that in my query? And do I have to use the forms!frmName!
fieldName statement?

See:
Subquery basics: Year to date
at:
http://allenbrowne.com/subquery-01.html#YTD

The article explains what a subquery is, and how to use one to get the value
for another period. The example shows the year-to-date, but the principle is
the same for getting the previous month.

I have a query, where it will ask for the current year and the previous
year

[quoted text clipped - 44 lines]

Kim P

 

[Allen Browne]

You can use [Forms].[frmName].[fieldName] in the Criteria of your query, and
you can use it in the subquery as well.

I recommend that you explicitly declare the parameter: in query design view,
choose Parameters on the Query menu, and enter 2 rows in the dialog, e.g.:
[Forms].[MyForm].[txtStartDate] Date/Time
[Forms].[MyForm].[txtEndDate] Date/Time

Method 1 of this article provides more info:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

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

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

Having never done a subquery before, I will be excited to try this! Now,
my
report is being based off of criteria(dates Start & End) entered on a
screen.
Should I depict that in my query? And do I have to use the forms!frmName!
fieldName statement?

See:
Subquery basics: Year to date
at:
http://allenbrowne.com/subquery-01.html#YTD

The article explains what a subquery is, and how to use one to get the
value
for another period. The example shows the year-to-date, but the principle
is
the same for getting the previous month.

I have a query, where it will ask for the current year and the previous
year

[quoted text clipped - 44 lines]

Kim P

 

[klp via AccessMonster.com]

Great, thank you I will try that. One more question. What about this, I have
3 tables that I need in my query. My fields are tblCustOrders.SalespersonID,
tblCustOrders.ShipDate, tblShipLines.ShipID, tblShipLines.ShipQty.

Tables are tblCustOrders, tblShipLines, tblOrderLines. tblCustOrders.OrderID
joins tblOrderLines.OrderID then tblOrderLines.ShipID joins tblShipLines.
ShipID. What tables do I need to include where? Meaning, which ones in my
main query and which in my sub?

You can use [Forms].[frmName].[fieldName] in the Criteria of your query, and
you can use it in the subquery as well.

I recommend that you explicitly declare the parameter: in query design view,
choose Parameters on the Query menu, and enter 2 rows in the dialog, e.g.:
[Forms].[MyForm].[txtStartDate] Date/Time
[Forms].[MyForm].[txtEndDate] Date/Time

Method 1 of this article provides more info:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Having never done a subquery before, I will be excited to try this! Now,
my

[quoted text clipped - 19 lines]

 

[Allen Browne]

Create the main query first, without worrying about the previous month's
values at this stage. It will be a Total query, where you group by the year
and month, and sum the total.

Once you have that working, switch it to SQL View (View menu, in query
design.) The subquery will be someone similar, but for the previous month.
You will need to alias the table names in the subquery.

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

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

Great, thank you I will try that. One more question. What about this, I
have
3 tables that I need in my query. My fields are
tblCustOrders.SalespersonID,
tblCustOrders.ShipDate, tblShipLines.ShipID, tblShipLines.ShipQty.

Tables are tblCustOrders, tblShipLines, tblOrderLines.
tblCustOrders.OrderID
joins tblOrderLines.OrderID then tblOrderLines.ShipID joins tblShipLines.
ShipID. What tables do I need to include where? Meaning, which ones in my
main query and which in my sub?

You can use [Forms].[frmName].[fieldName] in the Criteria of your query,
and
you can use it in the subquery as well.

I recommend that you explicitly declare the parameter: in query design
view,
choose Parameters on the Query menu, and enter 2 rows in the dialog, e.g.:
[Forms].[MyForm].[txtStartDate] Date/Time
[Forms].[MyForm].[txtEndDate] Date/Time

Method 1 of this article provides more info:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Having never done a subquery before, I will be excited to try this! Now,
my

[quoted text clipped - 19 lines]

Kim P

 

[klp via AccessMonster.com]

Okay I have finally been able to get back to my query design. I create the
subquery but I keep getting an error message that states: "The Select
statement includes a reserved word or argument name that is misspelled,
missing or incorrect punctuation". Having never done a subquery I'm not sure
if I did something wrong here. This is my syntax.

Select tblShipLines.ShipID, tblCustOrders.SalesPersonID,tblCustOrders.
ShipDate, Sum([ShipLines].[ShipQty]*[tblShipLines].[UnitPrice]) As ExtAmount,
Sum(tblShipLines.ShipQty) As SumOfShipQty,
(Select SL.ShipID, CO.SalesPersonID, CO.ShipDate, Sum(SL.ShipQty * SL.
UnitPrice) As
PrevExtAmount, Sum(SL.ShipQty) as PrevSumOfShipQty
From tblCustOrders As CO Left Join (tblShipLines As SL Right Join
tblOrderLines as OL on
SL.OrderLineID = OL.OrderLineID) On CO.OrderID = OL.OrderID
Group By SL.ShipID, CO.SalesPersonID, CO.ShipDate
Where(CO.ShipDate) >= [Forms]![frmReportShippingTotalsbyDate]!
[PrevStartDate] AND <=
[Forms]![frmReportShippingTotalsbyDate]![PrevStartDate]
From tblCustOrders Left Join (tblShipLines Right Join tblOrderLines on
OrderLineID = OrderLineID) On OrderID = OrderID
Group By ShipID,SalesPersonID, ShipDate
Where([tblCustOrders.].[ShipDate]) >= [Forms]![frmReportShippingTotalsbyDate]!
[CurStartDate] AND <= [tblCustOrders].[ShipDate] <=
[Forms]![frmReportShippingTotalsbyDate]![CurStartDate]))

Sorry if this is so difficult to read!

Create the main query first, without worrying about the previous month's
values at this stage. It will be a Total query, where you group by the year
and month, and sum the total.

Once you have that working, switch it to SQL View (View menu, in query
design.) The subquery will be someone similar, but for the previous month.
You will need to alias the table names in the subquery.

Great, thank you I will try that. One more question. What about this, I
have

[quoted text clipped - 28 lines]

 

[Allen Browne]

Your subquery does not meet the requirement of returning at most ONE field.

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

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

Okay I have finally been able to get back to my query design. I create the
subquery but I keep getting an error message that states: "The Select
statement includes a reserved word or argument name that is misspelled,
missing or incorrect punctuation". Having never done a subquery I'm not
sure
if I did something wrong here. This is my syntax.

Select tblShipLines.ShipID, tblCustOrders.SalesPersonID,tblCustOrders.
ShipDate, Sum([ShipLines].[ShipQty]*[tblShipLines].[UnitPrice]) As
ExtAmount,
Sum(tblShipLines.ShipQty) As SumOfShipQty,
(Select SL.ShipID, CO.SalesPersonID, CO.ShipDate, Sum(SL.ShipQty *
SL.
UnitPrice) As
PrevExtAmount, Sum(SL.ShipQty) as PrevSumOfShipQty
From tblCustOrders As CO Left Join (tblShipLines As SL Right Join
tblOrderLines as OL on
SL.OrderLineID = OL.OrderLineID) On CO.OrderID = OL.OrderID
Group By SL.ShipID, CO.SalesPersonID, CO.ShipDate
Where(CO.ShipDate) >= [Forms]![frmReportShippingTotalsbyDate]!
[PrevStartDate] AND <=
[Forms]![frmReportShippingTotalsbyDate]![PrevStartDate]
From tblCustOrders Left Join (tblShipLines Right Join tblOrderLines on
OrderLineID = OrderLineID) On OrderID = OrderID
Group By ShipID,SalesPersonID, ShipDate
Where([tblCustOrders.].[ShipDate]) >=
[Forms]![frmReportShippingTotalsbyDate]!
[CurStartDate] AND <= [tblCustOrders].[ShipDate] <=
[Forms]![frmReportShippingTotalsbyDate]![CurStartDate]))

Sorry if this is so difficult to read!

Create the main query first, without worrying about the previous month's
values at this stage. It will be a Total query, where you group by the
year
and month, and sum the total.

Once you have that working, switch it to SQL View (View menu, in query
design.) The subquery will be someone similar, but for the previous month.
You will need to alias the table names in the subquery.

Great, thank you I will try that. One more question. What about this, I
have

[quoted text clipped - 28 lines]

Kim P