Group By Record Limit in Query?

[Rick Lewis]

I am trying to query our database to return the specified customer & invoice
data within a given date range. The problem is that when we have an invoice
which I know has more than 10 line items, the query only returns 10 records
for that invoice, giving me inaccurate invoice totals. Below is the query
code.

Any help is much appreciated!

SELECT tbl_CM.Cust_Nbr, tbl_CM.Name, tbl_IVH_Hist.InvoiceDt,
tbl_IVH_Hist.InvNbr, tbl_IVD_Hist.Part_Nbr, tbl_IVD_Hist.QtyShipped,
tbl_IVD_Hist.ProdChrg, tbl_IVD_Hist.ProdCost, tbl_CM.SIC,
tbl_IVD_Hist.InvNbr, tbl_CM.Country
FROM ((((tbl_CM INNER JOIN tbl_IVH_Hist ON tbl_CM.Cust_Nbr =
tbl_IVH_Hist.CustNbr) INNER JOIN tbl_IVD_Hist ON tbl_IVH_Hist.InvNbr =
tbl_IVD_Hist.InvNbr) INNER JOIN tbl_IM ON tbl_IVD_Hist.Part_Nbr =
tbl_IM.Part_Nbr) INNER JOIN tbl_IM_Ven ON tbl_IM.Part_Nbr =
tbl_IM_Ven.Part_Nbr) INNER JOIN tbl_IM_CustPartNum ON tbl_CM.Cust_Nbr =
tbl_IM_CustPartNum.Cust_Nbr
GROUP BY tbl_CM.Cust_Nbr, tbl_CM.Name, tbl_IVH_Hist.InvoiceDt,
tbl_IVH_Hist.InvNbr, tbl_IVD_Hist.Part_Nbr, tbl_IVD_Hist.QtyShipped,
tbl_IVD_Hist.ProdChrg, tbl_IVD_Hist.ProdCost, tbl_CM.SIC,
tbl_IVD_Hist.InvNbr, tbl_CM.Country
HAVING (((tbl_IVH_Hist.InvoiceDt) Between [start date] And [end date]));

 

[Duane Hookom]

I don't see anything in your query that would limit the number of records.
What have you tried in order to troubleshoot? Have you tried changing any of
the joins to LEFT or RIGHT joins?

 

[Rick Lewis]

Hello Duane,

Thanks for the reply.

I have had success by making a sub query to get the invoice history fields,
then using that query to tie into the customer info. That seems to have fixed
the problem.

I appreciate your time.

Best regards,
Rick

I don't see anything in your query that would limit the number of records.
What have you tried in order to troubleshoot? Have you tried changing any of
the joins to LEFT or RIGHT joins?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I am trying to query our database to return the specified customer & invoice
data within a given date range. The problem is that when we have an invoice
which I know has more than 10 line items, the query only returns 10 records
for that invoice, giving me inaccurate invoice totals. Below is the query
code.

Any help is much appreciated!

SELECT tbl_CM.Cust_Nbr, tbl_CM.Name, tbl_IVH_Hist.InvoiceDt,
tbl_IVH_Hist.InvNbr, tbl_IVD_Hist.Part_Nbr, tbl_IVD_Hist.QtyShipped,
tbl_IVD_Hist.ProdChrg, tbl_IVD_Hist.ProdCost, tbl_CM.SIC,
tbl_IVD_Hist.InvNbr, tbl_CM.Country
FROM ((((tbl_CM INNER JOIN tbl_IVH_Hist ON tbl_CM.Cust_Nbr =
tbl_IVH_Hist.CustNbr) INNER JOIN tbl_IVD_Hist ON tbl_IVH_Hist.InvNbr =
tbl_IVD_Hist.InvNbr) INNER JOIN tbl_IM ON tbl_IVD_Hist.Part_Nbr =
tbl_IM.Part_Nbr) INNER JOIN tbl_IM_Ven ON tbl_IM.Part_Nbr =
tbl_IM_Ven.Part_Nbr) INNER JOIN tbl_IM_CustPartNum ON tbl_CM.Cust_Nbr =
tbl_IM_CustPartNum.Cust_Nbr
GROUP BY tbl_CM.Cust_Nbr, tbl_CM.Name, tbl_IVH_Hist.InvoiceDt,
tbl_IVH_Hist.InvNbr, tbl_IVD_Hist.Part_Nbr, tbl_IVD_Hist.QtyShipped,
tbl_IVD_Hist.ProdChrg, tbl_IVD_Hist.ProdCost, tbl_CM.SIC,
tbl_IVD_Hist.InvNbr, tbl_CM.Country
HAVING (((tbl_IVH_Hist.InvoiceDt) Between [start date] And [end date]));