Hi All
I have a form with a listbox that shows a query, the query builds from two tables (FRT_Table & FRT_Additionals_Table). The current query SQL is below:
Code:
SELECT FRT_Table.ID, FRT_Table.[POL Name], FRT_Table.[POD Name], FRT_Table.Carrier, FRT_Table.[Contract Type], FRT_Table.Contract, [FRT_Table].[20GP Cost]+[FRT_Additionals_Table].[20GP BAF]+[FRT_Additionals_Table].[20GP GRI]+[FRT_Additionals_Table].[20GP PSS]+[FRT_Additionals_Table].[20GP MISC] AS [20GP All In], [FRT_Table].[40GP Cost]+[FRT_Additionals_Table].[40GP BAF]+[FRT_Additionals_Table].[40GP GRI]+[FRT_Additionals_Table].[40GP PSS]+[FRT_Additionals_Table].[40GP MISC] AS [40GP All In], [FRT_Table].[40HC Cost]+[FRT_Additionals_Table].[40HC BAF]+[FRT_Additionals_Table].[40HC GRI]+[FRT_Additionals_Table].[40HC PSS]+[FRT_Additionals_Table].[40HC MISC] AS [40HC All In], FRT_Table.[Valid From], FRT_Table.[Valid To], FRT_Table.TransitFROM FRT_Table INNER JOIN FRT_Additionals_Table ON FRT_Table.Carrier = FRT_Additionals_Table.Carrier
WHERE (((FRT_Table.[POL Name])=Forms![Test Form]!POLCombo Or Forms![Test Form]!POLCombo Is Null) And ((FRT_Table.[POD Name])=Forms![Test Form]!PODCombo Or Forms![Test Form]!PODCombo Is Null) And ((FRT_Table.Carrier)=Forms![Test Form]!CarrierCombo Or Forms![Test Form]!CarrierCombo Is Null) And ((FRT_Table.[Valid To])>=Date()));
This is working so far and shows the right data etc in the listbox.
The next step however is working with valid dates, each table has a field for "Valid From" & "Valid To" this forms a date range where the record is valid.
Snippets of the two tables below for reference:
In the query (which shows on a listbox) I have three columns that combine three sets of values together:
- 20GP All In: [FRT_Table].[20GP Cost]+[FRT_Additionals_Table].[20GP BAF]+[FRT_Additionals_Table].[20GP GRI]+[FRT_Additionals_Table].[20GP PSS]+[FRT_Additionals_Table].[20GP MISC]
- 40GP All In: [FRT_Table].[40GP Cost]+[FRT_Additionals_Table].[40GP BAF]+[FRT_Additionals_Table].[40GP GRI]+[FRT_Additionals_Table].[40GP PSS]+[FRT_Additionals_Table].[40GP MISC]
- 40HC All In: [FRT_Table].[40HC Cost]+[FRT_Additionals_Table].[40HC BAF]+[FRT_Additionals_Table].[40HC GRI]+[FRT_Additionals_Table].[40HC PSS]+[FRT_Additionals_Table].[40HC MISC]
Currently the listbox will show duplicate rows for each date range in FRT_Additionals_Table (since I added in extra FRT_Additionals months), a snippet of the listbox below:
The result I need however is 1 row based on FRT Valid From/Valid To, with the correct FRT_Additionals added in. So the correct result from the above listbox would be as below mock up:
POL Name |
POD Name |
Carrier |
Contract Type |
Contract |
20GP All In |
40GP All In |
40HC All In |
Valid From |
Valid To |
Singapore |
Sydney |
ANL |
FAK |
April Test |
204 |
408 |
462 |
1/4/20 |
30/4/20 |
Singapore |
Sydney |
ANL |
FAK |
May Test |
240 |
480 |
570 |
1/5/20 |
31/5/20 |
Singapore |
Sydney |
ANL |
FAK |
June Test |
500 |
1000 |
1500 |
1/6/20 |
30/6/20 |
The above mock up combines the right FRT cost (in date range) with the right Additionals (in the date range)
Hope that explains it well enough.
I assume something needs to happen in the custom fields of the query to get for both date ranges? Any help / pointers would be greatly appreciated.
I have also attached my access file if you want to take a look at the whole thing.
Cheers