Did the basic query with just QTY STATUS TRANSTYPE as the only fields but it's freezing Access each time
Did the basic query with just QTY STATUS TRANSTYPE as the only fields but it's freezing Access each time
You need to link the two tables.
If you are still having issues with the data, does that first post query get rid of dups and give you the records to consider when doing the QTY sum? If so, save that query without any conditions or sums, just the GroupBy. Then create a 2nd query based on that first query, add only those 3 fields and do what I said in post 4. Or you could use that query to do a DSum:
DSum("Qty", "YourQueryName", "[Transtype] = 'L' And [STATUS] In(A,B,C,D,E,F,G,H)"
Aytee111 and I have given you a few different ways so see which one works. But you should get your recordset correct before trying to do totals and such.
To link tables the data has to be exactly the same on both sides. Which field is exactly the same in both tables? It is usual to name them the same so that there is no confusion.
IDK how we would have negatives though, everything should be 0 or positive
IDK how you got more than one record from the above query.
Can you tell us in simple, plain English --no Access jargon -- what are you trying to do?
Remember we don't know you, your environment or your business.
Let's identify WHAT before we keep guessing and working with HOW.
The goal is to try and get the total QTY, when Transtype is L and when Status is ABCDEFGH
Only certain project numbers [NUMBERS] in projmast is wanted. Those with L and ABCDEFGH
As I said we(I specifically) don't know our environment or issue, so clarity is key.
What exactly does this mean
Status is A or B or CThe goal is to try and get the total number of hours, which is "QTY", when Transtype is L and when Status is ABCDEFGH
OR
Status ="ABCDEFGH"
Any detail/context you can provide clearly will be helpful. People are here and willing to help, but not to guess repeatedly at what you are trying to do.
QTY is quantity of hours
Each time someone posts hours worked, they enter it into QTY. Im trying to total up QTY for project numbers where Transtype = L and when the project category is either ABCDEFG or H