MSAccess query problem - Newbie

I am trying to list movement of parts in an out of stock in a given month. I just want to list the part number and the total quantity in and out on one line. I can do this if I create a report but I can’t figure out how to achieve this if I output to a spreadsheet. Any help would be appreciated.
Howard LathamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
aikimarkCommented:
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
0
PatHartmanCommented:
Have you tried exporting the query that you used as the RecordSource for the report?
1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

John TsioumprisSoftware & Systems EngineerCommented:
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
0
Howard LathamAuthor Commented:
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
0
aikimarkCommented:
Please post a representative sample of the data
0
PatHartmanCommented:
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.
0
Howard LathamAuthor Commented:
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.
0
PatHartmanCommented:
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.
0
Howard LathamAuthor Commented:
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.
0
PatHartmanCommented:
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
0
Howard LathamAuthor Commented:
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]));
0
Dale FyeCommented:
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

Open in new window

0
Dale FyeCommented:
Then you might need to add a parameters clause to the query, something like:
Parameters [Beginning Date] date, [Ending Date] date;
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

Open in new window

You can also add the parameters by right clicking in the grey portion of the query design grid and selecting the 'Parameters' option.  This will popup a dialog that will allow you to enter the parameter name (as used in the query, including brackets) and the data type.

anoter option would be to change those parameters so that they refer to controls on a form so that you don't have to enter these values when the query is run:
Parameters [Forms]![YourFormName]![Beginning Date] date, [Forms]![YourFormName]![Ending Date] date;
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Howard LathamAuthor Commented:
Thank you Dale, that solved my problem, I am so grateful. Thank you Pat for all your help, it is much appreciated.
0
PatHartmanCommented:
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;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.