Query ??

[silverxx12]

Hi Not sure about this ..



I have two tables one with data and one which i want to use for to create the following query.

I want to use table 1 which is joined to table 2 (similar names in both)

Table 1 - looks something like this

name Text

John 5 Light Projector @£150

So it contains a name and a text string eg light projector / heavy projector / med projector with a rate eg @ £200

Table 2 is a list of all the different types of projectors which also have different rates depending on the name of the person.

So table 2 will look like

name type rate

John Heavy projector £300
John Light projector £250

What i need to do is modify the text string on table one as the rate is incorrect - i need it to lookup on table 2 the "name" and the "type" and replace the the rate after the @ sign with the correct rate.

So the output would look like John 5 Light Projector @£250

I cant modify table 1 - as its an output of data which is wrong and need to be modified regularly

Not exactly sure about this one appreciate all the help you can give

Thanks

[orange]

I cant modify table 1 - as its an output of data

Output from what where??

What are you really trying to do --- the big picture?

[silverxx12]

the big picture is im trying to replace the incorrect rates at the end of each text string with the correct rates

so instead of it saying

John 5 Light Projector @£150

I need it to say

John Light projector @£250

just not sure how to do this

"Output from what where??"

its a report i recieve in excel which i need to modify

[orange]

Why not change the data at source? Changing it post processing (reporting) will have you doing this every time you get the bad data.
Do you have the concept of "authoritative data" at your workplace? Some data is "sacred"/"the source you all agree to" --- that where you change the rate on the product.

If you are talking a database, then it seems you have a People table, a Product table, and a loaned/rented table. That was the big picture I was referencing.

[silverxx12]

Hi thats the problem i cant change it at source at least not for the time being

i need to find a work around to modify the output i get - not ideal but was hoping i can setup something in access so that i will not have to keep modifying it - i could jsut append the data table and run the query ?