Working with Valid date ranges

[Stildawn]

Hi All

Working on building a Freight Rate database in Access.

I have the attached so far, and for form (Test Form) works as intended, however it doesnt take into account the valid from/valid to fields on each record.

Basically need the below:
- FRT_Table, holds individual freight rates as records
- FRT_Additionals_Table holds individual additional costs that need to be added to the freight rates in FRT_Table
- Locals_Table holds individual local costs that need to be showing on the bottom of the form (test form) when a freight record is selected in the list box

- Each of the tables records have a valid from/valid to date
- The listbox in the Test Form, needs to show relevant freight records (from FRT_Table), and then for columns "20GP All In" / "40GP All In" / "40HC All In" it needs to add the Freight rates from FRT_Table + all the relevant additionals from FRT_Additionals_Table (BAF/GRI/PSS/MISC) to create a "All In" cost. So 20GP All In = 20GP Cost (FRT_Table) + 20GP BAF (FRT_Additionals_Table) + 20GP GRI (FRT_Additionals_Table) + 20GP PSS (FRT_Additionals_Table) + 20GP MISC (FRT_Additionals_Table)

- However it needs to check the valid from/to dates of the freight record and make sure it adds the correct FRT_Additionals based on their valid dates.

- The valid from/to dates on any of the tables can be any date, they are not set periods (e.g 1 month) it could be any set of dates.

Anyone able to help me on this?

Thanks

 

[ranman256]

Make a form that has 2 text boxes....txtStartDate, txtEndDate.
The base query Q1 pulls this via:
Select * from table where [datefield] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate.

Then in another query, Q2,use Q1 joined to other tables to wet more data.

 

[Stildawn]

Thanks, not sure how this would work for this project.

The user form has two options currently.

All valid rates from today() onwards
All rates including expired rates

So the user doesnt enter in the dates they want.

If the userform shows a bunch of records can I somehow filter another query in the background based on each record shown in the listbox? And then pull the data needed from that query? But would need to be a query for each record shown I think which could be many many records.