Updating field based on range

[Guest]

I have a table that contains category names and ranges of account codes to
which they refer as follows
CatName AccountCode Low AccountCiode Low
Sales 100 200
Purchases 300 400
Another table contains records that have account code field and a blank
field called category that i would like to be populated with CtName from
table above so that it looks like this
AccountCode Category
100 Sales
300 Purchases

Any help would be much appreciated.

 

[Pat Hartman \(MVP\)]

Rather than duplicating the data, use a query to return what you want from
the first table. Users should never be given queries to work with so if it
is column names that you are worried about, just change them in the form.
Use a datasheet form if you want it to look like a query/spreadsheet.

 

[Guest]

Thank you Pat for your response.

I understand your point about duplicating the data, but whilst I am fairly
proficient with relationships between data held in one field of each table I
have no idea how to specify the relationship when the data is held in one
field on one side of the relationship and two fields on the othe, so an
example would be much appreciated.

Regards

Peter

 

[Pat Hartman \(MVP\)]

You have to use a non-equi join. The QBE doesn't support this so build the
basic query to join to the category lookup table on AccountCode =
AccountCodeLow. Then switch to SQL view and where the Join says:

From tblA Inner Join tblB ON tblA.AccountCode = tblB.AccountCodeLow
Change it to:
From tblA Inner Join tblB ON tblA.AccountCode >= tblB.AccountCodeLow AND
tblA.AccountCode <= tblB.AccountCodeHigh

Once you make this change, you will not be able to go back to QBE view since
this join type cannot be represented graphically.

 

[Guest]

Thank you Pat! for a clear and precise answer that has helped me a lot.


regards

Peter