VBA Table Lookup

[cnother]

I am attempting to construct a database for the letting of holiday cottages and since I am relativley new to Access I would appreciate a pointer on how to proceed.

I have two tables (attached jpeg), the first (tblCottageBands) contains records of each cottage with the price/wk for each price band numbered 1 to n. The second table (tblBandDates) contains records with the price band n with a DateFrom and DateTo for the period that applies to that band.

If I have a StartDate and an EndDate for a cottage booking, how should I best approach the searching of the tblBandDates for the band that applies and subsequantly the tblCottageBands for the appropriate price band column in the record for the cottage? Can this be done using SQL or must I resort to VBA?
Thanks

 

[dcobau]

I would use combo boxes where cbo1 lists the cottages and cbo2 updates the information based on the selection in cbo1.

Dave

 

[cnother]

Dave,
not sure if this really addresses the problem. I've attached a screen-shot from the web of the Tariff matrix that I am trying to replicate in Access tables.

Thanks.

Chris

 

[dcobau]

Chris,

you need to get away from 'flat data' (spreadsheet) presentation and think database which is not something that you can do in a couple of days.

I would:

1) create 2 tables related to each other, Table1 contains details of your cottages and Table2 contains the range of dates.

2a) create a form with 2 combo boxes. cbo1 would list the cottage names and cbo2 would list the date ranges. When a value is selected in cbo1, cbo2 updates to show the values related to the value selected in cbo1. This is called cascading combo boxes.

or

2b) Have the form with only cbo1, then create a query to show what you want based on the value shown in cbo1. You can do it by inserting the fields you want shown in the criteria (including cottages) then have criteria in field cottages to say =forms!frmFormName!cbo1.

Dave