Results 1 to 5 of 5
  1. #1
    silverxx12 is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    18

    Query ??

    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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?

  3. #3
    silverxx12 is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    18
    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

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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.

  5. #5
    silverxx12 is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    18
    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 ?

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums