It's Huge! But that's what the customer wants!
SELECT tbl_Merchants.[Old MID], tbl_Merchants.[New MID],
tbl_Merchants.[New
MID Update], tbl_Merchants.[Updated MID], tbl_Merchants.PNS,
tbl_Merchants.[Merchant Name], tbl_Merchants.Address1,
tbl_Merchants.[Address1 Update], tbl_Merchants.City, tbl_Merchants.[City
Update], tbl_Merchants.State, tbl_Merchants.[State Update],
tbl_Merchants.Zip, tbl_Merchants.[Zip Update], tbl_Merchants.Phone,
tbl_Merchants.[Phone Update], tbl_Merchants.ContactFirst,
tbl_Merchants.ContactLast, tbl_Merchants.[Contact Update],
tbl_Merchants.MCC,
tbl_Merchants.[Updated Record], tbl_Merchants.[Info Updated],
tbl_Equipment.[POS Provided], tbl_Equipment.[Actual Terminal],
tbl_Equipment.[Actual Termina Update], tbl_Equipment.[TID#],
tbl_Equipment.[TID# Update], tbl_Equipment.[Application Name],
tbl_Equipment.[Application Name Update], tbl_Equipment.[Hypercom Mem],
tbl_Equipment.[Printer Type], tbl_Equipment.Imprinter,
tbl_Equipment.[Multi
Merchant], tbl_Equipment.[Touchtone Services], tbl_Equipment.[PC
Software],
tbl_Equipment.[PC Software Name], tbl_Equipment.[PC Software Version],
tbl_Equipment.[Multi Merchant], tbl_Equipment.[Check Service],
tbl_Equipment.[Ck Reader], tbl_Equipment.[Check Service Provider],
tbl_Equipment.[Check MID Num], tbl_Equipment.Debit, tbl_Equipment.[Debit
Update], tbl_Equipment.[Pin Pad Type], tbl_Equipment.[Pin Pad Serial
Number],
tbl_Equipment.AMEX, tbl_Equipment.[AMEX SE Num], tbl_Equipment.[AMEX
Update],
tbl_Equipment.DCVR, tbl_Equipment.[Discover MID Num], tbl_Equipment.[DCVR
Update], tbl_Equipment.DNRS, tbl_Equipment.[Diners MID Num],
tbl_Equipment.[Diners Update], tbl_Equipment.JCB, tbl_Equipment.[JCB MID
Num], tbl_Equipment.[JCB Update], tbl_Equipment.Table, tbl_Equipment.[Tip
Line], tbl_Equipment.Tab, tbl_Equipment.[Sales Tax],
tbl_Equipment.Invoice,
tbl_Equipment.[Footer Message], tbl_Equipment.[Access Num],
tbl_Equipment.Ticket, tbl_Equipment.Reference, tbl_Equipment.[Check In],
tbl_Equipment.[Check Out], tbl_Equipment.Folio,
tbl_Equipment.[Clerk/Server
Prompt], tbl_Equipment.[Auto Close], tbl_Equipment.[Auto Close Time],
tbl_CallData.[Call1 Date], tbl_CallData.[Call2 Date], tbl_CallData.[Call3
Date], tbl_CallData.[Call4 Date], tbl_CallData.[Call5 Date],
tbl_CallData.[Call Status Date], tbl_CallData.[Call Results],
tbl_CallData.Status, tbl_CallData.[Precall Completed Date],
tbl_CallData.[Referred Date], tbl_CallData.[Referred Reason],
tbl_CallData.[Escalated Date], tbl_CallData.[Escalation Reason],
tbl_CallData.[Completed By Client], tbl_CallData.[Closed Date],
[tbl_Precall
Appt Log].[Appt1 Date], [tbl_Precall Appt Log].[Appt2 Date], [tbl_Precall
Appt Log].[Appt3 Date], tbl_CallData.[Call1 Comment], tbl_CallData.[Logged
By], IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))) AS
MySorting
FROM ((tbl_Merchants LEFT JOIN tbl_Equipment ON tbl_Merchants.[New MID] =
tbl_Equipment.[Merchant Id]) LEFT JOIN tbl_CallData ON tbl_Merchants.[New
MID] = tbl_CallData.[Merchant ID]) LEFT JOIN [tbl_Precall Appt Log] ON
tbl_Merchants.[New MID] = [tbl_Precall Appt Log].[Merchant ID]
ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));
I am still so lost...Thanks!
Ken Snell said:
Please post the SQL statement of the entire query, not just the
calculated
field that we had discussed.
Or, use another calculated field, with a similar IIf construction, to
decide
which date field to use, and then sort on that calculated field too.
--
Ken Snell
<MS ACCESS MVP>
Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)
I need the query to list all records in the status order listed above,
and
in ascending order by date.
MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))
Thanks!