Criteria problem

G

Guest

This query runs great but I need 1 more criteria and I am not sure how to get
there. I need to put in a criteria that says if the lot number is the same
on the same date and the variety is the same then group these items together
also. Right now it lists everything. Any suggestions?

Thanks
Zenia

SELECT qryMergedData.[Run Order], qryMergedData.PostDate,
qryMergedData.RecvDate, qryMergedData.LotNum, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.TicketNum, qryMergedData.Bins,
qryMergedData.MaxFirm, qryMergedData.MinFirm, qryMergedData.AvgFirm,
qryMergedData.StdFirm, qryMergedData.P080, qryMergedData.P085,
qryMergedData.P090, qryMergedData.P095, qryMergedData.P100,
qryMergedData.P105, qryMergedData.P110, qryMergedData.P115,
qryMergedData.P120, qryMergedData.P130, qryMergedData.CullP,
qryMergedData.BrineP, qryMergedData.RDate, qryMergedData.Packed,
qryMergedData.Comments
FROM qryMergedData
GROUP BY qryMergedData.[Run Order], qryMergedData.PostDate,
qryMergedData.RecvDate, qryMergedData.LotNum, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.TicketNum, qryMergedData.Bins,
qryMergedData.MaxFirm, qryMergedData.MinFirm, qryMergedData.AvgFirm,
qryMergedData.StdFirm, qryMergedData.P080, qryMergedData.P085,
qryMergedData.P090, qryMergedData.P095, qryMergedData.P100,
qryMergedData.P105, qryMergedData.P110, qryMergedData.P115,
qryMergedData.P120, qryMergedData.P130, qryMergedData.CullP,
qryMergedData.BrineP, qryMergedData.RDate, qryMergedData.Packed,
qryMergedData.Comments
HAVING (((qryMergedData.Packed)=False))
ORDER BY qryMergedData.[Run Order], qryMergedData.TicketNum;
 
M

[MVP] S.Clark

Select LotNo, LotDate from Tablename GROUP BY LotNo, LotDate

Your SQL statement has many many fields in the GROUP BY clause, which could
potentially cause ALL records to show. For example, every Comment is
probably different for each record, thus having it in the Group By is
probably not good.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
 
G

Guest

This query returns values such as this
Date LotNum TicketNum VARIETY P85 P90 P95 P100 ETC ETC
05/05/05 34 1234 B .05 .6
..50 .33
05/05/05 34 2345 B .33 .5
..02 .25
05/06/05 50 3456 L .2 .5
..6 .7

These 3 ticket numbers I want to run and the query returns the correct value
but I also want it to group together if date, lotnum, and variety are the
same then add together.
is this possible

[MVP] S.Clark said:
Select LotNo, LotDate from Tablename GROUP BY LotNo, LotDate

Your SQL statement has many many fields in the GROUP BY clause, which could
potentially cause ALL records to show. For example, every Comment is
probably different for each record, thus having it in the Group By is
probably not good.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

esparzaone said:
This query runs great but I need 1 more criteria and I am not sure how to
get
there. I need to put in a criteria that says if the lot number is the
same
on the same date and the variety is the same then group these items
together
also. Right now it lists everything. Any suggestions?

Thanks
Zenia

SELECT qryMergedData.[Run Order], qryMergedData.PostDate,
qryMergedData.RecvDate, qryMergedData.LotNum, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.TicketNum, qryMergedData.Bins,
qryMergedData.MaxFirm, qryMergedData.MinFirm, qryMergedData.AvgFirm,
qryMergedData.StdFirm, qryMergedData.P080, qryMergedData.P085,
qryMergedData.P090, qryMergedData.P095, qryMergedData.P100,
qryMergedData.P105, qryMergedData.P110, qryMergedData.P115,
qryMergedData.P120, qryMergedData.P130, qryMergedData.CullP,
qryMergedData.BrineP, qryMergedData.RDate, qryMergedData.Packed,
qryMergedData.Comments
FROM qryMergedData
GROUP BY qryMergedData.[Run Order], qryMergedData.PostDate,
qryMergedData.RecvDate, qryMergedData.LotNum, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.TicketNum, qryMergedData.Bins,
qryMergedData.MaxFirm, qryMergedData.MinFirm, qryMergedData.AvgFirm,
qryMergedData.StdFirm, qryMergedData.P080, qryMergedData.P085,
qryMergedData.P090, qryMergedData.P095, qryMergedData.P100,
qryMergedData.P105, qryMergedData.P110, qryMergedData.P115,
qryMergedData.P120, qryMergedData.P130, qryMergedData.CullP,
qryMergedData.BrineP, qryMergedData.RDate, qryMergedData.Packed,
qryMergedData.Comments
HAVING (((qryMergedData.Packed)=False))
ORDER BY qryMergedData.[Run Order], qryMergedData.TicketNum;
Click to expand...
Click to expand...
 
J

John Spencer

It sounds as if you should be grouping by Date, LotNum, and Variety and
using Sum For P85, P90, P95, etc. I'm not sure what you would want to do
about ticket Num (Max, Min, remove it from the query altogether) and some of
the other fields.

SELECT qryMergedData.[Run Order], qryMergedData.PostDate,
qryMergedData.RecvDate, qryMergedData.LotNum, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.TicketNum, qryMergedData.Bins,
qryMergedData.MaxFirm, qryMergedData.MinFirm, qryMergedData.AvgFirm,
qryMergedData.StdFirm
, Sum(qryMergedData.P080) as SumP80
, Sum(qryMergedData.P085) as SumP85
, Sum(qryMergedData.P090) as SumP90
, Sum(qryMergedData.P095) as SumP95
, Sum(qryMergedData.P100 as SumP100
, Sum(qryMergedData.P105) as SumP105
, Sum(qryMergedData.P110) as SumP110
, Sum(qryMergedData.P115) as SumP115
, Sum(qryMergedData.P120) as SumP120
, Sum(qryMergedData.P130) as SumP130
, qryMergedData.CullP
, qryMergedData.BrineP
, qryMergedData.RDate
, First(qryMergedData.Packed) as Packd
, First(qryMergedData.Comments) as aComment
FROM qryMergedData
WHERE (((qryMergedData.Packed)=False))
GROUP BY qryMergedData.[Run Order], qryMergedData.PostDate,
qryMergedData.RecvDate, qryMergedData.LotNum, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.TicketNum, qryMergedData.Bins,
qryMergedData.MaxFirm, qryMergedData.MinFirm, qryMergedData.AvgFirm,
qryMergedData.StdFirm, qryMergedData.CullP,
qryMergedData.BrineP, qryMergedData.RDate
ORDER BY qryMergedData.[Run Order], qryMergedData.TicketNum;

esparzaone said:
This query returns values such as this
Date LotNum TicketNum VARIETY P85 P90 P95 P100 ETC
ETC
05/05/05 34 1234 B .05 .6
.50 .33
05/05/05 34 2345 B .33 .5
.02 .25
05/06/05 50 3456 L .2 .5
.6 .7

These 3 ticket numbers I want to run and the query returns the correct
value
but I also want it to group together if date, lotnum, and variety are the
same then add together.
is this possible

[MVP] S.Clark said:
Select LotNo, LotDate from Tablename GROUP BY LotNo, LotDate

Your SQL statement has many many fields in the GROUP BY clause, which
could
potentially cause ALL records to show. For example, every Comment is
probably different for each record, thus having it in the Group By is
probably not good.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

esparzaone said:
This query runs great but I need 1 more criteria and I am not sure how
to
get
there. I need to put in a criteria that says if the lot number is the
same
on the same date and the variety is the same then group these items
together
also. Right now it lists everything. Any suggestions?

Thanks
Zenia

SELECT qryMergedData.[Run Order], qryMergedData.PostDate,
qryMergedData.RecvDate, qryMergedData.LotNum, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.TicketNum, qryMergedData.Bins,
qryMergedData.MaxFirm, qryMergedData.MinFirm, qryMergedData.AvgFirm,
qryMergedData.StdFirm, qryMergedData.P080, qryMergedData.P085,
qryMergedData.P090, qryMergedData.P095, qryMergedData.P100,
qryMergedData.P105, qryMergedData.P110, qryMergedData.P115,
qryMergedData.P120, qryMergedData.P130, qryMergedData.CullP,
qryMergedData.BrineP, qryMergedData.RDate, qryMergedData.Packed,
qryMergedData.Comments
FROM qryMergedData
GROUP BY qryMergedData.[Run Order], qryMergedData.PostDate,
qryMergedData.RecvDate, qryMergedData.LotNum, qryMergedData.PoolNum,
qryMergedData.Variety, qryMergedData.TicketNum, qryMergedData.Bins,
qryMergedData.MaxFirm, qryMergedData.MinFirm, qryMergedData.AvgFirm,
qryMergedData.StdFirm, qryMergedData.P080, qryMergedData.P085,
qryMergedData.P090, qryMergedData.P095, qryMergedData.P100,
qryMergedData.P105, qryMergedData.P110, qryMergedData.P115,
qryMergedData.P120, qryMergedData.P130, qryMergedData.CullP,
qryMergedData.BrineP, qryMergedData.RDate, qryMergedData.Packed,
qryMergedData.Comments
HAVING (((qryMergedData.Packed)=False))
ORDER BY qryMergedData.[Run Order], qryMergedData.TicketNum;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

populate table through query or something??? 1

Top