Query to fill in missing information

[cstickman]

I am not sure if this is the right section to ask this question. So here is what I am doing. I am importing an Excel spreadsheet and three columns at times can be missing information depending on what is on the spreadsheet being imported. So the first three columns are type, topic and subtopic. The problem is that the spreadsheet may only have one of the three and not all of them. I want to add all three of them. The nice thing is that the spreadsheet will have at least one of the three. So I made another database and have all three of them filled out. So my plan was to use a DLookup and add the data based on whatever field was imported, but reading about DLookup the majority of users say to stay away from them as it will make the system run slow. The spreadsheet has 125 lines so thinking about it that could be tough to run on every line with a DLookup function. So I was thinking of maybe a query to add the missing information, but do not know how to set that up. Can anyone give a suggestion and maybe an example of how I could achieve this? So the type has 8 categories, topic has 120 and subtopic has about 115. So if they have a subtopic in the Excel spreadsheet I would like it to match it to the correct topic and then type. Then if the topic is only in the spreadsheet I would like it to look up the subtopic and type. Then the last thing would be if the only the Type is available it would fill in the topic and subtopic. I hope this makes sense, if not just ask me and I can try to explain it a different way. Thanks

 

[boerbende]

why not fixing the data in Excel with a VLOOKUP prior to importing. VLOOKUP is a very powerfull function, I guess I use it daily to add missing information, today again on 5000 records

 

[cstickman]

I would love to do your suggestion, but I am not the one uploading the spreadsheets. I am just the guy who has to organize it once it is in Access. I do not even know where the spreadsheets come from. I was just able to see one so I could write the queries.

 

[boerbende]

Yes, then it becomes a problem. But still... by doing this conversion you make yourself responsible for the data, while you are not ...
I would prefer to ask for complete and correct data.

Saying that I know this can be a burden.
What still is unclear is how to know when you have only one column given, what the rest should be. This can only be when this combination is unique
1) only type is given: then only 1 combination of topic / subtopic is possible
2) only subtopic is given: then only 1 combination of type / topic is possible
3) only topic is given: then only 1 combination of type / subtopic is possible

In that case: create a table (call it for example "tbl_test") with those columns and add as records all those unique combinations (which should be far less than the numbers you give here)

Create an update query based on these two tables (your import and this table with unique combinations.

UPDATE importexcel LEFT JOIN tbl_test ON importexcel.Type = tbl_test.Type SET importexcel.Topic = [tbl_test].[Topic], importexcel.Subtopic = [tbl_test].[Subtopic]
WHERE (((importexcel.Type) Is Not Null) AND ((importexcel.Topic) Is Null) AND ((importexcel.Subtopic) Is Null));

Redo this for the other two columns.

This is what I can think of, but maybe somebody else has a more efficient way of doing this.

Regards,

Ben

 

[cstickman]

Thank you for your help and I will play with it.