An alternative to FIRST and LAST in TOTALS query that also allows a secondary

[emamekyd]

Hello,

How would I go about displaying multiple dates on a TOTALS query that also require a WHERE clause? These are some of the fields needed:

· Date paid (BT Tracking: BT Request Date)
· Date BDI #1 Scheduled (BT Scheduling: Schedule Date) Note: Only use “Status”: Confirmed
· Date BDI #2 Scheduled (BT Scheduling: Schedule Date) Note: Only use “Status”: Confirmed
· Date BDI #3 Scheduled (BT Scheduling: Schedule Date) Note: Only use “Status”: Confirmed (At least 95% of the time no 3rd one is utilized; report will need to accommodate that fact)

(There are more fields needed and linked tables just as an FYI)

BT Tracking and BT Scheduling are linked by tracking_id, with BT scheduling using a sched_id and tracking_id as a compound key .

Sometimes there are 0 BDI's scheduled, sometimes there are 4 so it is difficult.
·

 

[plog]

You kinda lost me. Are we working with a query or a report?

If this is a report, the way to handle a 1-many relationship is by using a sub-report. Then you can allow it to grow/shrink as necessary.

If this is about a query, please demonstrate your issue with data. Provide 2 sets--

A. starting sample data from the underlying data sources. Include table/field names and enough data to cover all cases.

B. Expected results based on A. Show what you hope to end with in your query when you feed it the data from A.

 

[emamekyd]

Sorry about that, I dont want to overbear with information. Im working with a query

A)

BTTRACKING
tracking_id [number - ex: "12345"] primary key
bt_name [string - ex: "BFR"]
part_number [string - ex : "15-1551-ABC"]
invoice_code [number - ex: "12345"]
etc. etc. this table is quite large

BTSCHEDULING
tracking_id [same as above]
sched_id [number - ex "123456"] key along w/ tracking_id
sched_date [date - ex "09/21/2016"]
sched_status [string - ex "Confirmed"]


There can be multiple sched_id's for one tracking_id (one to many)
Also there can be multiple 'schedulings' for one day

B)

How I would like it displayed is - grouped by part number :

Part Number - Date Scheduled 1 - Date scheduled 2 - Date scheduled 3 -etc.
1234-CBA 9/19/2016 09/20/2016 09/20/2016

 

[plog]

How I would like it displayed is - grouped by part number :

Part Number - Date Scheduled 1 - Date scheduled 2 - Date scheduled 3 -etc.
1234-CBA 9/19/2016 09/20/2016 09/20/2016

No where in A do I any of those expected values. There's no way to achieve B with the data in A.

Please post better data. B needs to demonstrate what the results of A will be. Don't try and talk me through it, demonstrate it with data. Use this format for posting data:

TableNameHere
Field1Here, Field2Here, Field3Here, ....
Tom, 1/2/2016, 17
Larry, 12/31/2012, 91

 

[emamekyd]

Sorry I thought I replied to this last week sometime! Does this look better?
BTTRACKING
tracking_id, bt_name, part_number, invoice_code, date_used etc...
11000, B, 1234-ABC-56, 07891, ...
12000, F, 5678-DEF-90, 09999
13000, B, 2233-ABC-4455, 9595

BTSCHEDULING
tracking_id, sched_id, sched_date, sched_status
11000, 12507, 1/1/2016, Confirmed
11000, 12555, 2/1/2016, Confirmed
11000, 12568, 2/5/2016, Pending
12000, 12509, 1/5/2016, Confirmed
12000, 13008, 3/26/2016, Confirmed
12000, 13100, 4/5/2016, Confirmed
13000, 14000, 4/1/2016, Confirmed
13000, 14005, 4/3/2016, Pending


I'd like for it to show :

part_number, date_sched (first), date_sched (second), date_sched (third)
1234-ABC-56, 1/1/2016, 2/1/2016
5678-DEF-90, 1/5/2016, 3/26/2016, 4/5/2016
2233-ABC-4455, 4/1/2016

 

[plog]

Yes, that's good sample data. To get the data you want in a query, it's going to take 2 sub-queries, and this thing is going to be slow to run because it includes a domain function (DCount and a Cross-tab query). So the more data you have the longer this thing is going to churn. Here's the first sub query SQL:

SELECT BTTRACKING.part_number, BTSCHEDULING.sched_date
FROM BTSCHEDULING INNER JOIN BTTRACKING ON BTSCHEDULING.tracking_id = BTTRACKING.tracking_id
WHERE (((BTSCHEDULING.sched_status)="Confirmed"));

Name that 'Confirmations_sub1'. It identifies the records you want to work with and pulls the 2 tables together. Next is this query:

SELECT Confirmations_sub1.part_number, Confirmations_sub1.sched_date, DCount("[part_number]","Confirmations_sub1","[part_number]='" & [part_number] & "' AND [sched_date]<=#" & [sched_date] & "#") AS DateOrder
FROM Confirmations_sub1;

Name it 'Confirmations_sub2'. It orders your records so it knows which date is 1st, 2nd, 3rd, etc. Lastly, this SQL will provide you the data you want:

TRANSFORM Max(Confirmations_sub2.sched_date) AS MaxOfsched_date
SELECT Confirmations_sub2.part_number
FROM Confirmations_sub2
GROUP BY Confirmations_sub2.part_number
PIVOT "date_sched" & [DateOrder];

Let me know if you find any issues.