# More MAX PartTran.TranDate problems in BAQ **URL:** \ **Category:** Kinetic ERP\ **Tags:** baq\ **Created:** [July 14, 2023, 3:31pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047 "2023-07-14T15:31:16Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![rturrentine](https://www.epiusers.help/user_avatar/www.epiusers.help/rturrentine/32/35889_2.png) [@rturrentine](https://www.epiusers.help/u/rturrentine)\ **Post date:** [July 14, 2023, 3:31pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/1 "2023-07-14T15:31:17Z")
Another new task, another pain. I’ve been asked to create a dashboard for Sales. They want the part number product group, last date the part was purchased or manufactured, the job number or PO number, extend costs and vendor, and a few other minor fields. I created the BAQ following @kcrusen on this post, [E10.0 - SubQuery return maximum transaction date (Bin Ex.)](https://www.epiusers.help/t/e10-0-subquery-return-maximum-transaction-date-bin-ex/37480/5) I started with Part and added a sub-query for Purchase transaction using PartTran and filtering for PUR-STK. I linked the two queries and made a calculated filed in the top query for max(PURSTK.PartTran\_TranDate). This worked and I have all the parts listed with the last Purchased date listed next to purchased parts. As soon as I add another filed from my sub-query my BAQ blows up with multiple rows. It seems any iteration of adding another field blows up the BAQ. Here’s my current Phrase build, I haven’t added the second sub-query for MFG part yet. ![image](https://www.epiusers.help/uploads/default/original/3X/7/8/782f8a204fdc5ba5a03eec0ad581119c78ccd94f.png) And here’s my query phrase: ````auto /* * Disclaimer!!! * This is not a real query being executed, but a simplified version for general vision. * Executing it with any other tool may produce a different result. */ select [Part].[PartNum] as [Part_PartNum], [Part].[TypeCode] as [Part_TypeCode], [Part].[ProdCode] as [Part_ProdCode], [ProdGrp].[ProdGrup_Description] as [ProdGrup_Description], (max(PURSTK.PartTran_TranDate)) as [Calculated_MaxPurDate] from Erp.Part as Part left outer join (select [ProdGrup].[Company] as [ProdGrup_Company], [ProdGrup].[ProdCode] as [ProdGrup_ProdCode], [ProdGrup].[Description] as [ProdGrup_Description] from Erp.ProdGrup as ProdGrup group by [ProdGrup].[Company], [ProdGrup].[ProdCode], [ProdGrup].[Description]) as ProdGrp on ProdGrp.ProdGrup_Company = Part.Company and ProdGrp.ProdGrup_ProdCode = Part.ProdCode left outer join (select [PartTran].[Company] as [PartTran_Company], [PartTran].[PartNum] as [PartTran_PartNum], [PartTran].[TranQty] as [PartTran_TranQty], [PartTran].[UM] as [PartTran_UM], [PartTran].[ExtCost] as [PartTran_ExtCost], [PartTran].[PONum] as [PartTran_PONum], [PartTran].[POLine] as [PartTran_POLine], [Vendor].[VendorID] as [Vendor_VendorID], [Vendor].[Name] as [Vendor_Name], [Vendor].[PhoneNum] as [Vendor_PhoneNum], [PartTran].[TranDate] as [PartTran_TranDate], [PartTran].[TranNum] as [PartTran_TranNum] from Erp.PartTran as PartTran inner join Erp.Vendor as Vendor on PartTran.Company = Vendor.Company and PartTran.VendorNum = Vendor.VendorNum where (PartTran.TranType = 'PUR-STK') group by [PartTran].[Company], [PartTran].[PartNum], [PartTran].[TranQty], [PartTran].[UM], [PartTran].[ExtCost], [PartTran].[PONum], [PartTran].[POLine], [Vendor].[VendorID], [Vendor].[Name], [Vendor].[PhoneNum], [PartTran].[TranDate], [PartTran].[TranNum]) as PURSTK on PURSTK.PartTran_Company = Part.Company and PURSTK.PartTran_PartNum = Part.PartNum group by [Part].[PartNum], [Part].[TypeCode], [Part].[ProdCode], [ProdGrp].[ProdGrup_Description] order by Part.PartNum >``` ```` ---
**Author:** ![rturrentine](https://www.epiusers.help/user_avatar/www.epiusers.help/rturrentine/32/35889_2.png) [@rturrentine](https://www.epiusers.help/u/rturrentine)\ **Post date:** [July 14, 2023, 3:46pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/2 "2023-07-14T15:46:00Z")
To give full context of what Sales is asking and I’m planning to give. They had asked for a report of every part number (all 63,724) with the purchased or manufactured information including Part number, description, product group, last transaction date, PO and line number, job number (if MFG), trans quantity, extended costs, vendor and vendor address / phone number. My plan was to build a dashboard for them to filter by product group and maybe type (P or M) so the dashboard didn’t crash from the size. I intended to build the query with 2 sub-queries, one for PUR-STK transactions and another for MFG-STK. I would link them to the Part top level query so every part number was available and the last transaction for each type (P or M) would display with the relevant information. Does this sound sane? It’s been a humbling week so far, I haven’t had much success with these projects and it’s truly showing me how little I understand of the workings inside Epicor. ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [July 14, 2023, 3:57pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/3 "2023-07-14T15:57:17Z")
> [@rturrentine](#): > > Does this sound sane? It’s been a humbling week so far, I haven’t had much success with these projects and it’s truly showing me how little I understand of the workings inside Epicor. You’re on the right track. 🤔 ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [July 14, 2023, 4:03pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/4 "2023-07-14T16:03:06Z")
I’m sure someone will beat me to it, and I hope they do, but I’ve basically built the same thing before. Let me ponder and see if I have any tips. ---
**Author:** ![rturrentine](https://www.epiusers.help/user_avatar/www.epiusers.help/rturrentine/32/35889_2.png) [@rturrentine](https://www.epiusers.help/u/rturrentine)\ **Post date:** [July 14, 2023, 4:07pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/5 "2023-07-14T16:07:35Z")
Thanks. So for S&G I threw the entire BAQ together as I think it should go. It looks like my subqueries are returning every tran date instead of the last tran date. Here’s my query: ![image](https://www.epiusers.help/uploads/default/original/3X/e/6/e6e3eab927af8d2f547f4dc0416655d5bdf90f1b.png) This time I did my calculated field for MAX trandate on the subqueries instead of the top level query: ![image](https://www.epiusers.help/uploads/default/original/3X/a/4/a42d8f6305b75d2ce890e3a95f47b12473a7757f.png) And my Query Phrase ````auto * Disclaimer!!! * This is not a real query being executed, but a simplified version for general vision. * Executing it with any other tool may produce a different result. */ select [Part].[PartNum] as [Part_PartNum], [Part].[TypeCode] as [Part_TypeCode], [Part].[ProdCode] as [Part_ProdCode], [ProdGrp].[ProdGrup_Description] as [ProdGrup_Description], [PURSTK].[Calculated_MaxPurDate] as [Calculated_MaxPurDate], [PURSTK].[PartTran_PONum] as [PartTran_PONum], [PURSTK].[PartTran_POLine] as [PartTran_POLine], [PURSTK].[PartTran_TranQty] as [PartTran_TranQty], [PURSTK].[PartTran_UM] as [PartTran_UM], [PURSTK].[PartTran_ExtCost] as [PartTran_ExtCost], [PURSTK].[Vendor_VendorID] as [Vendor_VendorID], [PURSTK].[Vendor_Name] as [Vendor_Name], [PURSTK].[Vendor_PhoneNum] as [Vendor_PhoneNum], [MFGSTK1].[Calculated_MaxMFGDate] as [Calculated_MaxMFGDate], [MFGSTK1].[MFGSTK_JobNum] as [MFGSTK_JobNum], [MFGSTK1].[MFGSTK_TranQty] as [MFGSTK_TranQty], [MFGSTK1].[MFGSTK_UM] as [MFGSTK_UM], [MFGSTK1].[MFGSTK_ExtCost] as [MFGSTK_ExtCost] from Erp.Part as Part left outer join (select [ProdGrup].[Company] as [ProdGrup_Company], [ProdGrup].[ProdCode] as [ProdGrup_ProdCode], [ProdGrup].[Description] as [ProdGrup_Description] from Erp.ProdGrup as ProdGrup group by [ProdGrup].[Company], [ProdGrup].[ProdCode], [ProdGrup].[Description]) as ProdGrp on ProdGrp.ProdGrup_Company = Part.Company and ProdGrp.ProdGrup_ProdCode = Part.ProdCode left outer join (select [PartTran].[Company] as [PartTran_Company], [PartTran].[PartNum] as [PartTran_PartNum], [PartTran].[TranQty] as [PartTran_TranQty], [PartTran].[UM] as [PartTran_UM], (max(PartTran.TranDate)) as [Calculated_MaxPurDate], [PartTran].[PONum] as [PartTran_PONum], [PartTran].[POLine] as [PartTran_POLine], [PartTran].[ExtCost] as [PartTran_ExtCost], [Vendor].[VendorID] as [Vendor_VendorID], [Vendor].[Name] as [Vendor_Name], [Vendor].[PhoneNum] as [Vendor_PhoneNum], [PartTran].[TranDate] as [PartTran_TranDate], [PartTran].[TranNum] as [PartTran_TranNum] from Erp.PartTran as PartTran inner join Erp.Vendor as Vendor on PartTran.Company = Vendor.Company and PartTran.VendorNum = Vendor.VendorNum where (PartTran.TranType = 'PUR-STK') group by [PartTran].[Company], [PartTran].[PartNum], [PartTran].[TranQty], [PartTran].[UM], [PartTran].[PONum], [PartTran].[POLine], [PartTran].[ExtCost], [Vendor].[VendorID], [Vendor].[Name], [Vendor].[PhoneNum], [PartTran].[TranDate], [PartTran].[TranNum]) as PURSTK on PURSTK.PartTran_Company = Part.Company and PURSTK.PartTran_PartNum = Part.PartNum left outer join (select [MFGSTK].[Company] as [MFGSTK_Company], [MFGSTK].[PartNum] as [MFGSTK_PartNum], [MFGSTK].[TranType] as [MFGSTK_TranType], (max(MFGSTK.TranDate)) as [Calculated_MaxMFGDate], [MFGSTK].[TranQty] as [MFGSTK_TranQty], [MFGSTK].[UM] as [MFGSTK_UM], [MFGSTK].[JobNum] as [MFGSTK_JobNum], [MFGSTK].[ExtCost] as [MFGSTK_ExtCost], [MFGSTK].[SysDate] as [MFGSTK_SysDate], [MFGSTK].[TranNum] as [MFGSTK_TranNum] from Erp.PartTran as MFGSTK where (MFGSTK.TranType = 'MFG-STK') group by [MFGSTK].[Company], [MFGSTK].[PartNum], [MFGSTK].[TranType], [MFGSTK].[TranQty], [MFGSTK].[UM], [MFGSTK].[JobNum], [MFGSTK].[ExtCost], [MFGSTK].[SysDate], [MFGSTK].[TranNum]) as MFGSTK1 on MFGSTK1.MFGSTK_Company = Part.Company and MFGSTK1.MFGSTK_PartNum = Part.PartNum group by [Part].[PartNum], [Part].[TypeCode], [Part].[ProdCode], [ProdGrp].[ProdGrup_Description], [PURSTK].[Calculated_MaxPurDate], [PURSTK].[PartTran_PONum], [PURSTK].[PartTran_POLine], [PURSTK].[PartTran_TranQty], [PURSTK].[PartTran_UM], [PURSTK].[PartTran_ExtCost], [PURSTK].[Vendor_VendorID], [PURSTK].[Vendor_Name], [PURSTK].[Vendor_PhoneNum], [MFGSTK1].[Calculated_MaxMFGDate], [MFGSTK1].[MFGSTK_JobNum], [MFGSTK1].[MFGSTK_TranQty], [MFGSTK1].[MFGSTK_UM], [MFGSTK1].[MFGSTK_ExtCost] order by Part.PartNum>``` ```` ---
**Author:** ![rturrentine](https://www.epiusers.help/user_avatar/www.epiusers.help/rturrentine/32/35889_2.png) [@rturrentine](https://www.epiusers.help/u/rturrentine)\ **Post date:** [July 14, 2023, 4:10pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/6 "2023-07-14T16:10:04Z")
My results, a few of the 183,000 lines, 3X the number of parts. ![image](https://www.epiusers.help/uploads/default/original/3X/f/9/f90797edcfd436207187126b3da8e4cc629ec0e5.png) The circled items are duplicates. ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [July 14, 2023, 4:22pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/7 "2023-07-14T16:22:51Z")
You need to do a max in your subqueries on the date, something like this: ```sql select [Part].[PartNum] as [Part_PartNum], [Part].[ClassID] as [Part_ClassID], [Part].[TypeCode] as [Part_TypeCode], [SubQuery2].[PartTran_PartNum] as [PartTran_PartNum], [SubQuery2].[PartTran_TranType] as [PartTran_TranType], [SubQuery2].[PartTran_TranQty] as [PartTran_TranQty], [SubQuery2].[Calculated_lasttrandate] as [Calculated_lasttrandate] from Erp.Part as Part left outer join (select [PartTran].[PartNum] as [PartTran_PartNum], [PartTran].[TranType] as [PartTran_TranType], [PartTran].[TranQty] as [PartTran_TranQty], (max(PartTran.TranDate)) as [Calculated_lasttrandate] from Erp.PartTran as PartTran where (PartTran.TranType = 'MFG-STK') group by [PartTran].[PartNum], [PartTran].[TranType], [PartTran].[TranQty]) as SubQuery2 on Part.PartNum = SubQuery2.PartTran_PartNum ``` ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [July 14, 2023, 4:29pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/8 "2023-07-14T16:29:52Z")
What I just posted will give you the last time a part was manufactured. Then you can build off of that. ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [July 14, 2023, 4:31pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/9 "2023-07-14T16:31:52Z")
My query is not complete, I have duplicates, but I forgot what I need to filter on 🤣 🤔 ---
**Author:** ![rturrentine](https://www.epiusers.help/user_avatar/www.epiusers.help/rturrentine/32/35889_2.png) [@rturrentine](https://www.epiusers.help/u/rturrentine)\ **Post date:** [July 14, 2023, 4:40pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/10 "2023-07-14T16:40:15Z")
Yes, When I replicate your query I get duplicates too. It’s OK, I think we’re close. Just have to eliminate the duplicates. ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [July 14, 2023, 4:57pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/11 "2023-07-14T16:57:23Z")
Do it on the last tranNum (`max(PartTran.TranNum)`) ```sql select [Part].[PartNum] as [Part_PartNum], [Part].[ClassID] as [Part_ClassID], [Part].[TypeCode] as [Part_TypeCode], [SubQuery2].[PartTran_TranType] as [PartTran_TranType], [SubQuery2].[PartTran_TranNum] as [PartTran_TranNum], [SubQuery2].[PartTran_TranDate] as [PartTran_TranDate], [SubQuery2].[Calculated_lasttrannum] as [Calculated_lasttrannum] from Erp.Part as Part left outer join (select [PartTran].[PartNum] as [PartTran_PartNum], [PartTran].[TranType] as [PartTran_TranType], [PartTran].[TranNum] as [PartTran_TranNum], [PartTran].[TranDate] as [PartTran_TranDate], (max(PartTran.TranNum)) as [Calculated_lasttrannum] from Erp.PartTran as PartTran where (PartTran.TranType = 'MFG-STK') group by [PartTran].[PartNum], [PartTran].[TranType], [PartTran].[TranNum], [PartTran].[TranDate]) as SubQuery2 on Part.PartNum = SubQuery2.PartTran_PartNum ``` ---
**Author:** ![josecgomez](https://www.epiusers.help/user_avatar/www.epiusers.help/josecgomez/32/55731_2.png) [@josecgomez](https://www.epiusers.help/u/josecgomez)\ **Post date:** [July 14, 2023, 5:13pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/12 "2023-07-14T17:13:46Z")
That’s cause you are displaying Job Info can’ do that and grup. ---
**Author:** ![rturrentine](https://www.epiusers.help/user_avatar/www.epiusers.help/rturrentine/32/35889_2.png) [@rturrentine](https://www.epiusers.help/u/rturrentine)\ **Post date:** [July 14, 2023, 5:22pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/13 "2023-07-14T17:22:09Z")
Thanks. I’m a bit confused. Why can’t I display job info? Is it the same with the PO information? So, I can show the last PartTran tran date but if I include any other information the BAQ starts duplicating rows. I can’t ungroup and of the fields, it keeps giving me errors if I do. Do I need to create a subquery to filter to last tran date and another for the PO and Job information? I want to argue the logic of this but Epicor has often shown me that 24 divided by apples often equals bicycles. ---
**Author:** ![josecgomez](https://www.epiusers.help/user_avatar/www.epiusers.help/josecgomez/32/55731_2.png) [@josecgomez](https://www.epiusers.help/u/josecgomez)\ **Post date:** [July 14, 2023, 5:29pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/14 "2023-07-14T17:29:35Z")
You cannot show Max(date) and also Job Num , PO etc from part tran cause that’s not how SQL grouping works. SQL aggregate grouping groups like fields… jobs are all going to be different so you’ll get one row per job and or PO. You can use some of the techniques that @Banderson outlined here in his presentation to do what you want like Windowing Function and Ranking. > [@BAQ Calculated fields insights presentation](https://www.epiusers.help/t/baq-calculated-fields-insights-presentation/103274): > > Hey Everyone, This is a recording done after the fact of my insights presentation due to the technical difficulties I had during the presentation. Now I was able to record all of the live things that I wanted to show. I will apologize for my fat head in the way for some of it, but I thought it would be better if you could see me talking instead of just a disembodied voice. And I didn’t feel like taking the time to figure out how to use active presenter well enough to do some editing, so you ge… ---
**Author:** ![klincecum](https://www.epiusers.help/user_avatar/www.epiusers.help/klincecum/32/100137_2.png) [@klincecum](https://www.epiusers.help/u/klincecum)\ **Post date:** [July 14, 2023, 5:35pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/15 "2023-07-14T17:35:07Z")
Yes, you can display everything you want, but you’ll have to break some of it out in pieces to build off of. Takes some thought. ---
**Author:** ![rturrentine](https://www.epiusers.help/user_avatar/www.epiusers.help/rturrentine/32/35889_2.png) [@rturrentine](https://www.epiusers.help/u/rturrentine)\ **Post date:** [July 14, 2023, 6:02pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/16 "2023-07-14T18:02:27Z")
Thank you for that explanation. I was never completely sure how grouping worked in BAQs. I’m studying the video this afternoon. ---
**Author:** ![hkeric.wci](https://www.epiusers.help/user_avatar/www.epiusers.help/hkeric.wci/32/52019_2.png) [@hkeric.wci](https://www.epiusers.help/u/hkeric.wci)\ **Post date:** [July 14, 2023, 6:04pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/17 "2023-07-14T18:04:13Z")
Also look into Sub-Selects I usually end up doing something like this, if I need more columns. ![image](https://www.epiusers.help/uploads/default/original/3X/4/5/452b21c4dce212827928f941c68dffa2024f597c.png) More Info: > [@Using Count() in BAQ - Incorrect Syntax](https://www.epiusers.help/t/using-count-in-baq-incorrect-syntax/63385/13): > > @tmcmullen you can also do Sub-Select SubQuery. Then you wouldnt need to do GROUP BY on your Top-Query. In a Nutshell: Make a Sub-Query but DO NOT Use it on the Designer On your TopLevel Query add the Sub-Query to a Calculated Field On the Sub-Query under SubQuery Criteria you can add WHERE Clause. Example: SubQuery1: SubQuery2: Result: [image] Basically in a simpler view it does SELECT (SELECT count(\*) FROM table WHERe...) as YourCalculate… ---
**Author:** ![kananga](https://www.epiusers.help/user_avatar/www.epiusers.help/kananga/32/80812_2.png) [@kananga](https://www.epiusers.help/u/kananga)\ **Post date:** [July 14, 2023, 7:20pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/18 "2023-07-14T19:20:34Z")
> [@josecgomez](#): > > You can use some of the techniques that @Banderson outlined here in his presentation to do what you want like Windowing Function and Ranking. Windowed functions are the way! So much easier to write and reason about and live with than `group by`. For example, from the first few lines of your query, you could do this instead: ```auto select [Part].[PartNum] as [Part_PartNum], [Part].[TypeCode] as [Part_TypeCode], [Part].[ProdCode] as [Part_ProdCode], [ProdGrp].[ProdGrup_Description] as [ProdGrup_Description], (max(PURSTK.PartTran_TranDate) over(partition by Part.PartNum) as [Calculated_MaxPurDate] from Erp.Part as.......... ``` `Partition by` sets the grouping for the `max` aggregate. Basically, we want all the `max`’s, partitioned by each distinct part number. Since the only thing we care about here is the most recent purchase date for the part number, we only need to `partition by` the part number. One nice example of why windowed functions are better is that you could add: ```auto ,(max(PURSTK.PartTran_TranDate) over(partition by Part.TypeCode) as [Calculated_TypeLastPurchased] ,(max(PURSTK.PartTran_TranDate) over(partition by Part.ProdCode) as [Calculated_ProdCodeLastPurchased] ``` Not that that’s really useful but it’s a nice example of how painless it is to get `max` or other aggregates over different groupings using windowed functions, and each field is aggregated in total isolation from other fields. ---
**Author:** ![rturrentine](https://www.epiusers.help/user_avatar/www.epiusers.help/rturrentine/32/35889_2.png) [@rturrentine](https://www.epiusers.help/u/rturrentine)\ **Post date:** [July 14, 2023, 9:48pm UTC](https://www.epiusers.help/t/more-max-parttran-trandate-problems-in-baq/105047/19 "2023-07-14T21:48:56Z")
Wow did this work for me. Thank you very much. I watched the video from @Banderson (several times) and was able to build the queries that he did. Then I was able to move over and recreate my BAQ using Windows Functions and it worked perfectly. While your help was greatly needed I am marking that video as a solution for anyone in the future looking for guidance on BAQs.