Seek Improved "Between" command and also collapsing my data

[knuckelhead]

In a Qry on the Invoice Date criteria line, i am using .... Between [Start Date] And [End Date]

There is a risk of error in answering the question.
I could correctly type 7/1/2005 as the Start data but incorrectly type 12/31/2004 as the End Date. And i may not realize my mistake.

So, Question #1 is there a way to double check my 2 date choices after i typed them in?

I next use a Second Qry to collapse the detail records into just one record per formula. Which makes me wonder to myself, Question #2 is there a way to make 1 Qry instead of 2 ?? Here is what I mean.

table data is:
Text Text Text Number Number Date

SoldtoID ShiptoID FormulaID Rev$ SellQty InvoiceDate
97 28 Blue Car 880 153 7/6/2005
97 28 Blue Car 1,265 220 7/13/2005
97 28 Blue Car 920 160 7/18/2005
97 28 Blue Car 690 120 7/27/2005
97 28 Blue Car 1,208 210 7/29/2005
97 28 Blue Car 805 140 7/29/2005


124 44 Red Boat 229 139 7/3/2005
124 44 Red Boat 363 220 7/3/2005
124 44 Red Boat 264 160 7/8/2005
124 44 Red Boat 198 120 7/17/2005
124 44 Red Boat 297 180 7/19/2005
124 44 Red Boat 231 140 7/23/2005


Desired is:
SoldtoID ShiptoID FormulaID Rev$ SellQty
97 28 Blue Car 5,768 1,003
124 44 Red Boat 1,582 959

In summary, can I have #1 a way to double check my date range input and #2 is there a way, maybe VBA, to collapse the 12 detail records into just 2 records?

i do not know vba.
thanks for your suggestion.

 

[Remou]

If you create a small form, say frmDateDialog, you can use it to check the dates. You could even have them default to the first and last of the current month, if that was suitable. The dates can then be used in a query. This:

SELECT tblTableX.SoldtoID, tblTableX.ShiptoID, tblTableX.FormulaID, Sum(tblTableX.[Rev$]) AS [SumOfRev$], Sum(tblTableX.SellQty) AS SumOfSellQty
FROM tblTableX
WHERE (((tblTableX.InvoiceDate) Between [Forms]![frmDateDialog].[txtStartDate] And [Forms]![frmDateDialog].[txtEndDate]))
GROUP BY tblTableX.SoldtoID, tblTableX.ShiptoID, tblTableX.FormulaID;

Should group your data to create two lines.

 

[stix4t2]

Remou is talking about a group by query. That is the greek sigma at the top of the query builder. When you toggle it on, the query summarizes information. It also adds an aggregate line to the query fields below. This is where you will specify the type of aggregate for each field. It can be a trial an error kind of deal till you find the query you are looking for.

As for the date stuff, if you are pulling values off a form then you can use validation to make sure that date end is > greater tahn date start.

 

[knuckelhead]

I will try and study these answers tomorrow. I am excited and can't wait to try the answers out.
Knucklehead

 

[knuckelhead]

I am back on the case. One thing I learned is that you want me to UnCheck the Invoice Date field and that the Between statement still runs. that is new to me.

I still cannot get a collapse.

i took out some fields in my real life query. Below is what I really have. I put it into SQL which you are gradually teaching me.

SELECT tblInvoice.SoldtoID, tblInvoice.ShiptoID, tblInvoice.FormulaID, Sum(tblInvoice.[Rev$]) AS [SumOfRev$], Sum(tblInvoice.SellQty) AS SumOfSellQty, Sum(Val(Round(nz([SellQty])*(nz([SellUnitHist])/nz([ConvFactorHist])),3))) AS StdQty
FROM tblInvoice
GROUP BY tblInvoice.SoldtoID, tblInvoice.ShiptoID, tblInvoice.FormulaID, tblInvoice.InvoiceDate
HAVING (((tblInvoice.InvoiceDate) Between [Start Date] And [End Date]))
ORDER BY tblInvoice.SoldtoID, tblInvoice.ShiptoID, tblInvoice.FormulaID, tblInvoice.InvoiceDate;


I still get a row for every invoice date, although it does not show a date, i get the monthly rows for a given soldtoId-ShiptoID-FormulaID which I call my 3Combo deal.
I am looking for the months per 3combo to collapse into just 1 row per 3combo with a grandtotal rev$ and Qty per 3combo.

I must be doing something wrong.

When I use REMOU's SqL code, i do in fact get a collapse. However i get a strange request for my start and end dates. but it works in the end. here is what remou has:

SELECT tblInvoice.SoldtoID, tblInvoice.ShiptoID, tblInvoice.FormulaID, Sum(tblInvoice.[Rev$]) AS [SumOfRev$], Sum(tblInvoice.SellQty) AS SumOfSellQty
FROM tblInvoice
WHERE (((tblInvoice.InvoiceDate) Between [Forms]![frmDateDialog].[txtStartDate] And [Forms]![frmDateDialog].[txtEndDate]))
GROUP BY tblInvoice.SoldtoID, tblInvoice.ShiptoID, tblInvoice.FormulaID;


If i try to add another field to Remou's method, then his method reverts back to many monthly rows per 3combo.
so something needs firming up I think. Can you look at this again?
thanks

 

[PHV]

You have to understand how an aggregate query works.
What is the REAL issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knuckelhead]

I've been doing aggregate Access queries for 8 years. but I don't know Sql. Remou's method works fine if I only use the 3combo code, the Rev$ and SellQty with the Invoice date unchecked with a "Between" statement.

The problem seems to develope when i want to do some calc work before the summing.

I wanted to add 2 more fields and a 3rd field that is calculated named StdQty. I assumed that i could make the calculated field StdQty as a "Total" Sum. Since the other 2 fields are the same for every comparable record, i figured that these 2 fields would just collapse as a Group By. Alas, i get a record for every month for a given 3combo code (soldtoID-shiptoID-formulaID).

I have gotten around this problem for 8 years by making 2 queries. The first qry shows all 12 months records Per 3combo and calculated the noted above stdQty. Then the 2nd Qry just Sums Rev$ and SellQty and StdQty with the 3 combo codes only. This second qry becomes the basis for other queries that need sales data per 3combo.

I was hoping to merge the 2 qry's into 1 qry. Remou's method would work if i did not need the other 3 fields to calculate StdQty.

Maybe you can glean something here. Or maybe i must stay with my 2 qry method: first get the detailed records and do some calc work and then use a 2nd Qry to Sum up the records to a 3combo level.

thanks for taking a look. If there is a way, only you guys can figure it out.

 

[PHV]

I was hoping to merge the 2 qry's into 1 qry
Why ?
Anyway it's probably feasible with inline view.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knuckelhead]

So maybe my 2 qry method is fine afterall, from what you say. i guess i thought i was doing something inefficient.
but you say that i am doing the 2 qry just fine. so that is good enough reinforcement for me.

thanks for taking a look.
Knucklehead

 

[PHV]

thanks for taking a look
Sorry but I've not seen your 2 queries, nor the 3rd based on ...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886