DLookup function needing Multiple Criteria

[SStrange]

I probably have the syntax off but, I am trying to figure out the best way to have a query look within the data it is using and pull the data relating to multiple fields without doing a traditional join.

DLookup seems to be the function I would need to use to do. I want to make sure all the existing data remains intact and if a field just doesn't have the field then for it populate a blank. As most of this is from financial data I am using P&Ls and the basic function structure of

DLookup("[Q1Actuals]","[Actuals]","Criteria")

Problem is I don't know how to get the multiple criteria to be populated. I understand for the first set I would use "[Account] = 'Total Sales'" if it was just the one but, as this is pulling from a table that combines multiple P&Ls into one master table, I can't get it to also take into account the different account IDs. Please help.

Thanks in advance...

 

[SStrange]

I do believe after further research that perhaps I need to look into some other way to handle this as I have tried quite a few ways to pull this data. If you can offer any other options I will be glad to entertain. I have been able to do this with multiple subqueries and using crosstabs to pull the information I was looking for but ultimately I don't think that will be the best answer (though it does work for the time being). If there is a way to do this I would still appreciate the assistance otherwise.

Thanks,

 

[Micron]

I'm sorry but your posts don't make a lot of sense (to me, anyway). You say you want to use a query, then you ask about the DLookup function, which does not produce an editable result, so what's the concern about "intact" data? How can the data be intact if you want to "populate" it with a blank? What's wrong with using a query, regardless if it involves joins? If the criteria values are limited for a field, you can put In("Total Sales", "Gross Sales", "Net Sales", "etc Sales") in the query criteria field for example. What you want should be easy - if we can understand the requirements and what you're working with a bit better.

 

[SStrange]

I understand that it may have been a bit vague. I was trying to understand the function and how I could use it to basically perform a lookup and populate a field not unlike a subquery which it kind of is but it doesn't matter for the purpose of what the ultimate goal is. Rather let me explain the issues that I am running into and then purhaps this would make more since.

I am trying to import mass records from some of our vendors and have it tie out our customer masters, tax rate masters, etc and pull the data into something I use for reporting and uploads for our SAP servers. I do have tabular templates for the uploads and that would obviously just be one function of the DB. Part of the problem is that we get have a customer master and location master and sometime our subcontract vendors don't maintain the service calls for an individual location. The customer may have 20 locations and they may go to 16. When I create the select query using the customer master table combined to the locations and whatnot, I can pull the larger information table and see all the locations but when I apply the vendor information it only applies the ones that they serviced during the term. I need it to still populate a status in the query showing that the location wasn't serviced. I am certain it is something simple that I am doing wrong. I know how to do most all of these things in excel but the amount of data I am handling is just too large for excel. I was trying to use the DLookup function to lookup a specific field from one table based on one fixed criteria (sales) and one variable criteria, customer ID. After some working with it and going back over the function, I don't think this is what I need to use becuase I think it is handling too much data even if I could get it to work. I think this would be better for the forms and that works just fine. Regardless, Any help would be appreciated.

 

[SStrange]

As I stated, I was sure it was something simple that I was doing incorrectly. After some digging I figured out how to do what I was looking to be able to do. Obviously, the DLOOKUP function was not the answer. I had a problem with the the join. Regardless, it is resolved and populating the data appropriately now.

 

[Micron]

Glad to read you solved it. Not sure if after all this you realize that the DLookup function can only retrieve what's in one field in one record and cannot be used to directly edit data on the fly. Only by using code or an action query that relies on the lookup's returned value could you even come close to say you are using the function to edit.

 

[SStrange]

I kind of figured that out. For whatever reason, I kept thinking it should able to read variable fields in my head and it can just not in the way I was wanting it to. I just wasn't thinking of it from the correct angle. I know how to get it to read in a form and get it to lookup multiple criteria that way. I think that is where the primary confusion was. I also wasn't taking into account some of the other data structure I built in with some of the other queries. Regardless, thanks for attempting to point me in the correct direction. I am obviously still learning and will be for some time.