MSAccess query problem - Newbie

We'd have to know more about your database before we could give you any advice. Can you let us know your table structure, or perhaps upload a copy of your database.

You might have three columns in your query
1. The sum of out-going transactions for an item
2. The sum of in-coming transactions for an item
3. The sum of the first two columns as the over-all increase/decrease for the month of that item

Have you tried exporting the query that you used as the RecordSource for the report?

The first thing to tackle is the correct INs and Out sum...if this is OK post back to take a look on  the Report Export

I have two tables in my query: Stock, which lists parts, and movement which records ins and outs. I am using two fields from my stock table: partx which is the part number and partdesc which is the description. I am using four fields from my movements table: qtyin, datein, qtyout, dateout and an expression to select the date range ((IIf(IsNull([DateIn]),[DateOut],[DateIn]))).

My problem is that each transaction starts on a new line (naturally) which is not what I want. I want the query to return total for the range.

Part No    Description            Total Qty In     Total Qty Out
A1              dongle                                      0                         19

Thanks, I hope you can help

Please post a representative sample of the data

Select PartNum, Desc, Sum(QtyIn) As TotalQtyIn, Sum(QtyOut) As TotalQtyOut
From yourtable
Where DateIn Between Forms!yourForm!startdate and Forms!yourform!enddate
Group By PartNum, Desc

Just FYI - you have two dates in the data.  Which date should be used to satisfy the criteria?  I picked DateIn but perhaps DateOut would be better.  If you want to use both dates, you will need two queries.  One to sum the qtyIn and the second to sum the QtyOut and you can union them to get them into a single recordset where you can sum them.

It still lists each transaction rather than giving me to the total in one line..I guess I’m doing it wrong. I’m sorry, I’m new to this.

If you are seeing detail rows, you are selecting some column that is preventing summation.  Post your query and a sample of data if you can.

Thank you for your help on this Dan, unfortunately I can’t send my data which is frustrating and unhelpful to you, I apologise for that.  I don’t think I can interpret your instructions correctly. my query works just fine as a printed report as I put the totals in the footer. The way I see it as a complete novice is that the query returns every instance instead of summing the grand total. What I need is to list,one time, the part number and description and the total of stock out and the total of stock in within a given date range but it is listing each entry, probably because of the date in and the date out, as I might have 10 instances of the same part number with zeros where there is no stock in or zeros where there is no stock out.

We can't guess as to what is wrong with the query.  I understand that English can sometimes be ambiguous (which is why programs are not written in a natural language but in code).  Perhaps this would have been clearer.

Post your query.   Also, a sample of data if you can.

If you think the query should be summarizing the data and it isn't, then you have included some column which is unique for each row and therefore cannot be summarized away.  To post the SQL, change the query to SQL view and copy the SQL string so you can paste it here.

Contrast this version of the query with the one I originally suggested.  This one includes DateIn.  That means that you will end up with multiple rows for each PartNum and Desc as long as there are different values of DateIn.

Select PartNum, Desc  DateIn,, Sum(QtyIn) As TotalQtyIn, Sum(QtyOut) As TotalQtyOut
From yourtable
Where DateIn Between Forms!yourForm!startdate and Forms!yourform!enddate
Group By PartNum, Desc

If you want to summarize to month, then this version would work:

Select PartNum, Desc  Format(DateIn, "yyyy-mm") AsYearMonth,, Sum(QtyIn) As TotalQtyIn, Sum(QtyOut) As TotalQtyOut
From yourtable
Where DateIn Between Forms!yourForm!startdate and Forms!yourform!enddate
Group By PartNum, Desc

Hello

I have attached my original query that works if I output to a report. I am using Access 2000 and I can't seem to recreate the query you sent me and  I can't send the data as I am bound by a confidentiality agreement. I am very grateful to you for the time you've spent on this.

SELECT Movement.partx AS [Part No], cookestock.partdesc AS Description, Sum(Movement.QtyIn) AS SumOfQtyIn, Sum(Movement.QtyOut) AS SumOfQtyOut
FROM cookestock INNER JOIN Movement ON cookestock.partx = Movement.partx
GROUP BY Movement.partx, cookestock.partdesc, ((IIf(IsNull([DateIn]),[DateOut],[DateIn])))
HAVING (((((IIf(IsNull([DateIn]),[DateOut],[DateIn])))) Between [Beginning Date] And [Ending Date]));

Well, since you are not including the Date parameter in the output, I would take that out of the Group By clause, and then I would move that to a WHERE clause, so that the date filter is performed before the aggregation, something like:

SELECT Movement.partx AS [Part No], 
cookestock.partdesc AS Description, 
Sum(Movement.QtyIn) AS SumOfQtyIn, 
Sum(Movement.QtyOut) AS SumOfQtyOut
FROM cookestock INNER JOIN Movement ON cookestock.partx = Movement.partx
WHERE IIf(IsNull([DateIn]),[DateOut],[DateIn]) Between [Beginning Date] And [Ending Date]
GROUP BY Movement.partx, cookestock.partdesc

Select allOpen in new window

Thank you Dale, that solved my problem, I am so grateful. Thank you Pat for all your help, it is much appreciated.

You somehow managed to get yourself a HAVING clause which Dale corrected for you BACK to the WHERE clause which I suggested.  I will explain the difference.
HAVING is applied to  the aggregated recordset and so is applied AFTER all the data is summarized.  Therefore, anything in the HAVING must be included in the Group By clause and that would force multiple rows to appear even though you would not see any difference because the select clause didn't include the date.  To use HAVING, make sure that your criteria applies to some aggregated value rather than an input field.  For example, you want all customers who have ordered more than 3 times in a single month:
Select Customer, Format(OrderDate, "yyyy/mm") as OrderMonth, Count(*) as OrderCount
From tblCustomerOrders
Group by Customer, Format(OrderDate, "yyyy/mm")
HAVING Count(*) > 3;

WHERE is applied BEFORE the data is aggregated so it reduces the number of rows that will need to be aggregated.  So, Where can be applied to any field in the source table whether or not it is selected.
This is the same query as above but it only selects data for this year.

Select Customer, Format(OrderDate, "yyyy/mm") as OrderMonth, Count(*) as OrderCount
From tblCustomerOrders
WHERE Year(OrderDate) = Year(Date())
Group by Customer, Format(OrderDate, "yyyy/mm")
HAVING Count(*) > 3;

Even if the field is included in the select clause, if it is not aggregated, it is more efficient to use the WHERE clause.  So even though the following two queries will produce identical results, the first query will do it much more efficiently than will the second.  Think about what happens if you have a million orders to slog through.  The first will use and index to pull out only orders for the specific customer so it might have to aggregate only a few dozen records.  The second will aggregate all of the million orders and then discard all those except for the specific customer.

Select Customer, Format(OrderDate, "yyyy/mm") as OrderMonth, Count(*) as OrderCount
From tblCustomerOrders
WHERE Customer = 20987
Group by Customer, Format(OrderDate, "yyyy/mm")
HAVING Count(*) > 3;

Select Customer, Format(OrderDate, "yyyy/mm") as OrderMonth, Count(*) as OrderCount
From tblCustomerOrders
Group by Customer, Format(OrderDate, "yyyy/mm")
HAVING Count(*) > 3 AND Customer = 20987;