How do I create a lookup table to modify data before sending to a report?

[bearcub]

I have a situation where I have some source data in a table that needs to be "massaged" before it goes into a report.

There is an intermediary table that is used query along with the source data.

The developer created a modified source data table that required a lot of work to maintain. I'm trying to simplify the process so we use the source data unchanged by creating necessary workarounds that can be easily updated and modified as required to generate the reports we need.

For example, I have numerous positions where the source data uses the term "Committee" in describing a position. Since the intermediary table is the one that is used for reporting, the position will be omitted in the query because it the source position and modified position doesn't match. Or, I have positions like "Vice - President" that show up in the report as "Vice-President".

Is there an easy way to create a V lookup type table (like you would in Excel) in Access where I use the source data as criteria to pull the other field into my query (or table)?

Another example:

I need to change source position data from "Member Engagement Chair" to "Member Chair". I need to exclude "Engagement" from my report.

Is there an easy way to do this in Access without modifying the source data?

Thank you for your help,

Michael

 

[bearcub]

As I'm thinking about it what I want to do is merge 1 fields from 2 different tables to create a 3rd field - the one to use. The source data is constantly being updated so I need a way to create something in Access that would be comparable to Vlookup or Index match in you're finding the original data but want a different result - like looking for Santa Claus but returning Santa or St Nick.

The lookup value is Santa Claus and the output is Santa or St Nick.

Hopefully what I have and what I need isn't confusing.

Michael

 

[Micron]

If I grasp the situation, you have source data with inconsistent values in a field and you want to standardize it. This won't be overly easy, but it's doable. Don't know if you want to scan the data and choose a combo value for the over-write or what. One approach for that way which comes to mind is a form with a listbox or combo of the standard terms/values. That might involve a datasheet subform whereby if you double click the record, it overwrites that field with the combo value.

Or you might want to be searching for all the records that contain a chosen combo value. These would be retrieved by using the LIKE operator in the subform datasheet recordset (whereby the field contains the combo value). Now what? Manually correct each record? Click a button to over-write all of the subform datasheet records with the combo value?

You'll have to give some thought as to how you envision the process playing out. Click that button with the wrong value in it (say you change it after generating a set of records). A combo AfterUpdate event might have to be written to ensure your data set is relevant to the combo selection. See what I mean?

 

[bearcub]

Yes, I see. I want to avoid combo boxes if I can help it because this is what created the problem in the first place. I think the solution is that I have to set up a table with the members ID and names with a field for the before and after. I think this is the only way that I could it without having to massage the data. I do have a table already set up with this information but I don't have one specifically designed like this. What I would like to see is to have the raw data imported into Access and have this table act as a liaison between that and the report. I can run an update query to populate that table which should keep the reports current.

Do you think that sounds like it would work?

 

[welshgasman]

If I understand you correctly.
Create a table that has at least two fields.
FiieldToFind
FieldToReturn

For ease of coding you would have

FieldToFind                                   FieldToReturn
Vice President                               Vice President
Member Engagement Chair                      Member Chair

Leave Engagement out of the table altogether and select the join where data matches in both tables.

Then bring in FieldToReturn into your query.

So in essence, put field titles you want to see into the table, amend the FieldToRetun fields for the required returned data, and omit any you do not want.

I must admit, this for me would be trial and error until I got what I wanted.

HTH

 

[Micron]

I thought the problem was wanting the data from the table with the wrong values in the field, but wanting to standardize them. Eliminating them from a recordset won't do that. Perhaps I misunderstand.

I want to avoid combo boxes if I can help it because this is what created the problem in the first place

That makes no sense unless they weren't utilized properly. Combos are used to prevent people from entering anything that's not in the list. However, you to have to set the Limit To List property to True/Yes.

As for your last question, again, perhaps I'm not getting the drift. If the Excel data is compromised but linked and you want to fix it before running the report, AFAIK you can't update a linked spreadsheet in Access. You'd either have to duplicate it in a native table and fix it there (if you want the fix to be permanent) either manually or via an Update query, or do so on the fly with a query that your report is based on. I see no sense in doing it on the fly each time the report runs, and you'd need a calculated field for every field that you need to correct - too messy. So an update query would have to run against an Access table and not a linked spreadsheet as I've already noted.

If you don't care to interact with the fix, then I just see a table with the proper values for the problem fields and an update query that would fix anything that is a partial match. So if you wanted Sales Manager and your bad data contains Manager,Sales for instance, then you update to Sales Manager where tlbMyTable.TheField LIKE *Manager*


To repeat, I'm on a different tack than welshgasman so maybe I have it all wrong.