HELP!!! Union Query - Adding IF where condition

[Shaunk23]

I have a union statement that displays in listbox... Its a shipping database. The below statement works fine.. Grabs all data it needs to & displays the data as the events are to happen... Goes into the future 20 days and the past 60 days. I have two combo boxes on the form with the list.

Sort By Job Manager & Sort By Tracking Group. Those Combo Boxes have the names of all current users/groups & then have a "ALL Users" or ALL Groups at the top.

I Need to be able to add this to my below union... I had tried putting:

((ExpRecords.[Job Manager])=IIf([Forms]![ExpOutBoundManager]![ExpOBMQueryManagerCmbo]='All Users',' ',[ExpRecords]![Job Manager]=[Forms]![ExpOutBoundManager]![ExpOBMQueryManagerCmbo]))

That doesnt work. I need to add IF the Combobox = ALL USERS then query as is... for all users, if not then Query using the Specific user selected.


[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, "Sail On Board" & " " & [ExpBooking]![VesselName] & " " & [ExpBooking]![VesselVoy] AS [Event Description], ExpBooking.DateOfDeparture AS [Date], [ExpBooking]![DateOfDeparture]-Date() AS Days, IIf([SailedConfirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.DateOfDeparture)<Date()+20 And (ExpBooking.DateOfDeparture)>Date()-60))[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, "Arrive Final Discharge Port" & " " & [ExpBooking]![VesselName] & " " & [ExpBooking]![VesselVoy] AS [Event Description], ExpBooking.[DateOfArrival] AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days,IIf([FinalArriveConfirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.[DateOfArrival])<Date()+20 And (ExpBooking.[DateOfArrival])>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, "Arrive Final OnCarriage Point" & " " & [ExpBooking]![VesselName] & " " & [ExpBooking]![VesselVoy] AS [Event Description], ExpBooking.OnCarriageDate AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days,IIf([DateOfArrival],"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC [/FONT]
[FONT=&quot]WHERE (((ExpBooking.OnCarriageDate)<Date()+20 And (ExpBooking.OnCarriageDate)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment1Event AS [Event Description], ExpBooking.Transhipment1Date AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days,IIf([Transhipment1Confirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.Transhipment1Date)<Date()+20 And (ExpBooking.Transhipment1Date)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC;[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment2Event AS [Event Description], ExpBooking.Transhipment2Date AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days,IIf([Transhipment2Confirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.Transhipment2Date)<Date()+20 And (ExpBooking.Transhipment2Date)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC;[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment3Event AS [Event Description], ExpBooking.Transhipment3Date AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days,IIf([Transhipment3Confirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.Transhipment3Date)<Date()+20 And (ExpBooking.Transhipment3Date)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC;[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment4Event AS [Event Description], ExpBooking.Transhipment4Date AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days, IIf([Transhipment4Confirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.Transhipment4Date)<Date()+20 And (ExpBooking.Transhipment4Date)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC;[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment5Event AS [Event Description], ExpBooking.Transhipment5Date AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days, IIf([Transhipment5Confirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.Transhipment5Date)<Date()+20 And (ExpBooking.Transhipment5Date)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC;[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment6Event AS [Event Description], ExpBooking.Transhipment6Date AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days, IIf([Transhipment6Confirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.Transhipment6Date)<Date()+20 And (ExpBooking.Transhipment6Date)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC;[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment7Event AS [Event Description], ExpBooking.Transhipment7Date AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days, IIf([Transhipment7Confirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.Transhipment7Date)<Date()+20 And (ExpBooking.Transhipment7Date)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC;[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]UNION [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]SELECT ExpRecords.AEC, ExpRecords.Shipper, ExpBooking.NvoccCarrier AS Carrier, ExpBooking.UltimateCarrier AS Line, [ExpBooking].[QuantityOfEquip] & " x " & [ExpBooking].[EquipmentType] AS Equipment, ExpBooking.Transhipment8Event AS [Event Description], ExpBooking.Transhipment8Date AS [Date], [ExpBooking]![DateOfArrival]-Date() AS Days, IIf([Transhipment8Confirm]=-1,"Yes","No") AS Confirm[/FONT]
[FONT=&quot]FROM (ExpRecords INNER JOIN ExpBooking ON ExpRecords.AEC = ExpBooking.AEC) INNER JOIN ExpDockRec ON ExpRecords.AEC = ExpDockRec.AEC[/FONT]
[FONT=&quot]WHERE (((ExpBooking.Transhipment8Date)<Date()+20 And (ExpBooking.Transhipment8Date)>Date()-60))[/FONT]
[FONT=&quot]ORDER BY Days DESC;[/FONT]

 

[Insane_ai]

Try executing the query from VBA. Start by analyzing the value of the combo box then build your where clause to include or exclude the username restriction. Build your union query using the same strings that you are using now then append the where clause as needed.

1. Build all the select statements into string variables without the where clause.
2. Read the combo box into a variable on the event, maybe a button click would be easy. strMyCombo = Forms![MyForm].[MyCombo]
3. Using an If Statement build the where clause to include or exclude the usename
4. rebuild your union query using the appropriate where clause:
strSQLUNION = strSQLSelect1 & strSQLWhere & "UNION" & strSQLSelect2 & StrSQLWhere..........

 

[Shaunk23]

Hey Insane,

Thanks I did try doing something along those lines. Are we sure its possible to use the " & "UNION" in a function or module? I had tried that earlier & couldnt get it to display. All my selects would work fine... Then when i put:

WHOLESQL = SQL1 & "Union" & SQL2 & "UNION & SQL3

and set the listbox rowsource to WHOLESQL i got NOTHING. I tested each of the SQL1 / SQL2 / SQL3 & they worked fine... Any ideas???

 

[Insane_ai]

1. Try strSQLSelect1 & " " & strSQLWhere & vbnewline & "UNION" &vbNewLine & strSQLSelect2 & " " & strSQLWhere .....

In essence you are building a long string that will be used with the DoCmd.RunSQL (string) command. I have found that adding the spaces between strings is needed to separate the commands in the SQL as it would be in a query designer.

Let me know if that works for you. Try it with the vnNewLine and with just an extra space before and after the union statement " UNION "

 

[jdraw]

Perhaps, if you described WHAT you were trying to accomplish in plain English, someone or many would post options as to How it might be done. Looking at a bunch of SQL is not necessarily everyone's first choice in understanding your situation.

Just my $.02

 

[Shaunk23]

It was described above.. In a VB function i have defined SQL1 & SQL2. I Then am setting WHOLESQL as SQL & " " & VBCRLF & "UNION" & VBCRLF & SQL2

Then setting listbox rowsource to WHOLESQL. Debug is above but its not working?

 

[Shaunk23]

I have tested both SQL & SQL2 and both work fine when set as row source... I need to combine them with UNION as WHOLESQL to set listsource... It returns NO records... ANY IDEA???

WholeSQL = SQL & vbCrLf & "Union" & " " & vbCrLf & SQL2


Debug.Print WholeSQL

With ExpOBMlist
.RowSource = SQL
.BoundColumn = 1
.ColumnWidths = ColAEC & ";" & 2900 & ";" & ColBooking & ";" & ColEquipment & ";" & 2000 & ";" & ColSailingdate & ";" & ColDocCut & ";" & ColITN
.ColumnCount = 10
.Requery
End With

 

[Shaunk23]

Maybe WholeSQL = ("SQL & vbCrLf & 'Union' & vbCrLf & SQL2 &") ?

 

[Insane_ai]

Now that I see the SQL I see the problem. The fields you are joining in the UNION query don't match. Although they may have similar data they do not have the same field names. Your Order by clauses don't agree either.

SQLSQL2AECAECShipperShipperCarriercarrierLinelineEquipmentequipmentEvent DescriptionEvent DescriptionSailing DateDate of ArrivalDaysDaysSailedConfirmArrivedPortConfirm


After looking at the fields, I have to ask why you would want to join these records? You have one set of departure information and one set of arrival information. These seem distinct to me and without seeing the rest of what you are building, I would think these should be separate fields.

 

[Insane_ai]

SQL _______________SQL2
AEC _______________AEC
Shipper _______________ Shipper
Carrier _______________ Carrier
Line _______________ Line
Equipment _______________ Equipment
Event Description _______________Event Description
Sailing Date _______________ Date of Arrival
Days _______________ Days
SailedConfirm _______________ ArrivedPortConfirm

 

[Shaunk23]

Hi Insane.. .Alright.. I have an international shipping company.. Each shipment would contain the following dates..

Sailing Date
Arrival Date
& THEY CAN include these:
OncarriageDate
Transhipment1
Transhipment2
Transhipment3
Transhipment4
Transhipment5
Transhipment6
Transhipment7
Transhipment8

What im building is a "traffic monitor" This query is designed to Pull all records together from the database who will have "AN EVENT" happen 20 days in the future counting down & up to 60 days in the past... So for instance..

If you have a shipment.. It was suppose to sail on 4/19/12 (yesterday) & arrive on 4/25/12 then your query would show

Shipment info | EVENT (Sailing) -- DAYS would be "-1" because the event should have happened yesterday.

The next line would show the same record info | EVENT (Arrive Final Port ) with Days being "5" as it will be happening in 5 days from now... Does that make sense?

Some shipments will have 0 transhipment events... some will have 5 & some 8. So i have to pull them all in together. These queries work by them selves.. just need to get them together.. Any ideas?

 

[Shaunk23]

I see what your saying but i had thought by naming them all "EVENT DATE" they would be the same. I could order by "days"

 

[Shaunk23]

Also Insane - Just to add... The listbox has a double click function to open to that record where the user can confirm these events as they happen... Other then that this query is only for Viewing Purposes.... Anyway you can think of to make this work?

 

[jdraw]

Do you have a data model ( Tables and relationships) that you could post as a jpg?

INsane ai has identified some pieces

Shipper
Carrier
Line
Equipment
Event Description
Sailing Date

I also see Shipment, Final Port, TransShipment...

 

[Shaunk23]

Relationships
[FONT=&quot]C:\Documents and Settings\Shaun\Desktop\AEC LOGISTICS\Program\Hard [/FONT][FONT=&quot] [/FONT][FONT=&quot]Friday, April 20, 2012[/FONT][FONT=&quot][/FONT]
[FONT=&quot]Program\AecLogistics.accdb[/FONT][FONT=&quot][/FONT]
[FONT=&quot]Relationships: All[/FONT][FONT=&quot] [/FONT][FONT=&quot]Page: 1[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Relationships[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]CashReceiptTicketCashReceiptLineItem[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]CashReceiptTicket[/FONT][FONT=&quot] [/FONT][FONT=&quot]CashReceiptLineItem[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ID[/FONT][FONT=&quot] [/FONT][FONT=&quot]CashReceiptTicketID[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Not Enforced[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpDockRecExpDocRecAuto[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpDockRec[/FONT][FONT=&quot] [/FONT][FONT=&quot]ExpDocRecAuto[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ID[/FONT][FONT=&quot] [/FONT][FONT=&quot]DocID[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Not Enforced[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecordsExpBooking[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecords[/FONT][FONT=&quot] [/FONT][FONT=&quot]ExpBooking[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecordsExpDockRec[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecords[/FONT][FONT=&quot] [/FONT][FONT=&quot]ExpDockRec[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecordsExpDocRecAuto[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecords[/FONT][FONT=&quot] [/FONT][FONT=&quot]ExpDocRecAuto[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot]
[/FONT] [FONT=&quot]C:\Documents and Settings\Shaun\Desktop\AEC LOGISTICS\Program\Hard [/FONT][FONT=&quot] [/FONT][FONT=&quot]Friday, April 20, 2012[/FONT][FONT=&quot][/FONT]
[FONT=&quot]Program\AecLogistics.accdb[/FONT][FONT=&quot][/FONT]
[FONT=&quot]Relationships: All[/FONT][FONT=&quot] [/FONT][FONT=&quot]Page: 2[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecordsExpDocsOut[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecords[/FONT][FONT=&quot] [/FONT][FONT=&quot]ExpDocsOut[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]aec[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecordsExpMoveOrders[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecords[/FONT][FONT=&quot] [/FONT][FONT=&quot]ExpMoveOrders[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecordsExpShipmentLog[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecords[/FONT][FONT=&quot] [/FONT][FONT=&quot]ExpShipmentLog[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecordsExpTransmittal[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]ExpRecords[/FONT][FONT=&quot] [/FONT][FONT=&quot]ExpTransmittal[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]AEC[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]MSysNavPaneGroupCategoriesMSysNavPaneGroups[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]MSysNavPaneGroupCate[/FONT][FONT=&quot] [/FONT][FONT=&quot]MSysNavPaneGroups[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Id[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]GroupCategoryID[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Updates, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]
[FONT=&quot]
[/FONT] [FONT=&quot]C:\Documents and Settings\Shaun\Desktop\AEC LOGISTICS\Program\Hard [/FONT][FONT=&quot] [/FONT][FONT=&quot]Friday, April 20, 2012[/FONT][FONT=&quot][/FONT]
[FONT=&quot]Program\AecLogistics.accdb[/FONT][FONT=&quot][/FONT]
[FONT=&quot]Relationships: All[/FONT][FONT=&quot] [/FONT][FONT=&quot]Page: 3[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]MSysNavPaneGroupsMSysNavPaneGroupToObjects[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]MSysNavPaneGroups[/FONT][FONT=&quot] [/FONT][FONT=&quot]MSysNavPaneGroupToO[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Id[/FONT][FONT=&quot] [/FONT][FONT=&quot]1[/FONT][FONT=&quot] [/FONT]¥[FONT=&quot] [/FONT][FONT=&quot]GroupID[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Attributes: [/FONT][FONT=&quot] [/FONT][FONT=&quot]Enforced, Cascade Updates, Cascade Deletes[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT][FONT=&quot]RelationshipType: [/FONT][FONT=&quot] [/FONT][FONT=&quot]One-To-Many[/FONT][FONT=&quot][/FONT]

 

[Shaunk23]

I don't have a site to share on.. I could email a copy or relationships table's etc if anyone is will to help that way.

 

[Insane_ai]

1. Is AEC a unique identifier for the shipments?
2. Are the Transshipment(1-8) fields in a single record or individual records related to each other?

I think you need to see all shipments that have left in the last 60 days and will dock somewhere in the next 20 days. You also want to see all scheduled stops for each shipment and possibly some detail when you double click the destination.

Am I close?

If I am, I would build a query that shows all shipments within the ranges specified in a combo box or list box. Once a selection is made, I would populate a second combo box or list box with the destinations scheduled that can be used to pull details of the shipments.

ANother option I can see is modifying your current structure in a single query where you pull all the fields and use a grouping by sail date. This may get the list but I'm not sure it would provide a complete solution.

Is there any way you can upload a copy of what you are working on with some sample (not live) data?

 

[jdraw]

Do you have anything to do a screen capture? Like IrfanView?

Just capture the Relationships screen and save as jpg, then attach using the Manage Attachments under the Go Advanced button.

 

[Shaunk23]

1) Yes AEC is Unique. Main table Exp Records has unique AEC. All parts of shipment (Expbooking,ExpDockReceipt,ExpMoveOrder are linked back by aec ) You can have just 1 booking but you can have many move orders / dock receipts. Each Dock receipt can have many cars.

2) Transhipment events are on the booking table. They are literally listed out
Transhipment Event 1
Transhipment Event 2
Transhipment Event 3
etc...

For Any shipment or AEC you could have a MAXIMUM of 8 transhipment events.

Id be happy to upload.. do you have a site recommended?? I Can email it compressed. Only a few test records are in there.

Your getting closer... It needs to see the "events" as sailling / arrive port / arrive final delivery / transhipment 1-8 events IF the user entered them in booking form.

When tracking query is opened the user shouldnt select any dates.. the query should pull ALL events applicable that will either be happening in the next 20 days or have happened in the past 60 days. Make sense???

 

[Shaunk23]

Here it is