Dlookup in a query.

[Eric2013]

I'm still not getting this dlookup function to work in my favour. Simple query, i have a master parts list, i have an equipment list. Equipment list has OEM # [Engine Oil Filter #1] with each unit [Tag #], the master parts list [MasterPrts] is my physical inventory each part has bin locations.

this is what i've used:
SELECT Equip1.[Tag #], Equip1.[Engine Oil Filter #1], DLookUp([Engine Oil Filter #1],[MasterPrts]) AS Expr1
FROM Equip1;






I know i'm missing the criteria part and maybe something else.

all i want to do is to have the [Engine Oil Filter #1] in Equip1.[Tag #] find it's "location" in [MasterPrts].

thanks in advance for any help.

[orange]

I think you have missed some quotes...
Here's a link to DLookup description, syntax and examples.

Good luck.

[Eric2013]

thanks Orange, i've tried that site a few times, but still keep getting errors. Used probably 20 different variations of assorted syntax and a few verbal ones of my own. i think i'm going to leave Dlookup alone, and just do manual entries. I have 44,000 part #'s, and only need to manually edit about 400. In my case, this seems a lot faster then wasting anyone else's time.

Thanks again for your reply.

Eric...

[pbaldy]

You certainly need a criteria, but you're getting an error because the query isn't named "MasterPrts", it's "MasterPrts Query". Plus it doesn't appear that the field you've specified in that query exists. You also need quotes around each argument.

[June7]

Manual entries where?

I think this is what you were trying for:

SELECT Equip1.*, DLookUp("Location", "[MasterPrts]", "[Part # OEM]='" & [Engine Oil Filter #1] & "'") AS PartLoc FROM Equip1;

But why DLookup? Why not build a query that joins tables? As Paul noted, the image shows MasterPrts Query and the SQL uses just MasterPrts table - which should be just fine if those are the native fields from the table.

SELECT Equip1.*, Location FROM Equip1 RIGHT JOIN MasterPrts ON Equip1.[Engine Oil Filter #1]=MasterPrts.[Part # OEM];

Why are there two Equip tables?

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[Eric2013]

hey guys, once again, tks for your help. Most likely still something i'm doing wrong. I've renamed the query and tables so there were no spaces and added a field for "Location" in Equip1Qry. Thanks for the previous tips on field names. Then i altered SELECT Equip1.*, DLookUp("Location", "[MasterPrts]", "[Part # OEM]='" & [Engine Oil Filter #1] & "'") AS PartLoc FROM Equip1;

to SELECT [Equip1Qry].*, DLookUp("Location", "[MasterPrtsQry]", "[Part # OEM]='" & [Engine Oil Filter #1] & "'") AS PartLoc FROM Equip1Qry;

to SELECT [Equip1Qry].*, DLookUp("Location", "[MasterPrtsQry]", "[Part # OEM]='" & [Engine Oil Filter #1] & "'") AS Part Location FROM Equip1Qry;

get an error that says "The syntax of the subquery in this expression is incorrect".

it's not allowing me to upload copy of what i'm working with.

The 2 equip tables are because i ran out of fields in equip1. Something i should have thought of when i first started this database, but like anything else, it just keeps growing.

I have equipment that i load into the database using an equipment fill in form. There is where i list what quick moving parts like filters, belts, recommended services are added to that particular piece. Then i have a master parts table, and again, it get's loaded with a parts fill in form.

Then i open up a new work order, key in the tag of the machine and it fills in all the info using combo boxes gathering info from the tables i need to complete that particular job. The exercise i'm trying to perform using Dlookup, is to place the location of the parts next to the part #. The tech fills in what they've used for parts, and adds in the labour, and a description of the work completed and i have a finished job.

Parts are delivered to me on a daily basis. They are physically placed in a parts room, then entered into an excel spread sheet for tracking purposes, location, how many sold per month, per year etc. from that spread sheet, i do an import into my database, run a duplicates query and i'm good til the next. i have created a report that prints off barcode labels which we place on parts bins. Once a week, i go to these bins, using my iphone, i scan the barcodes and enter the qty's, then send the file to my database. HOpefully in the near future i get an official data Terminal Collector, but the current way i'm doing works fine.

If i build a query that joins 2 tables, how will it line up the proper parts with the proper machine?

i really don't know if this is a good way to do it or not, but it's working with minimal effort at this point. I have looked into business systems to handle what i'm doing and cant' seem to find any that i like. Now that i have built this database, i can't see any use for another system. When i get to a the next stage in my company, i will have to split this database allowing only the front end for use with the techs. and of course , a transactions table.

Hope i answered your previous questions properly.

Tks in advance. Eric...

[June7]

Still spaces and special characters in naming.

Your revised SQL has a space in the alias field name - need []: [Part Location]

Did you try the suggested query with table join?

Why can't you upload? Did you follow the instructions at bottom of my post?

Manufacturing type db has been topic of numerous threads. Not an easy db to build.

A forum search on "manufacturing" keyword and user "June7" returns 20 threads. Here is one https://www.accessforums.net/databas...oin-28334.html

[pbaldy]

Regarding

"The 2 equip tables are because i ran out of fields in equip1. Something i should have thought of when i first started this database, but like anything else, it just keeps growing."

what do the fields represent? You almost certainly have a normalization problem.

[Eric2013]

Solved.!! tks June7.

SELECT [Equip1Qry].*, DLookUp("Location", "[MasterPrtsQry]", "[Part # OEM]='" & [Engine Oil Filter #1] & "'") AS PartLoc FROM Equip1Qry;

[June7]

The query with join would be a better approach. Domain aggregates in query can slow performance.

I agree with Paul's observation - this data structure is not normalized and will likely cause lots of frustration.

[Eric2013]

alright, let's talk about the query to join the 2 tables. how is the master parts table going to put the proper bin location with the part #? i can start a new thread and try to post my sample database at that time. I really don't know why it won't let me upload it in this thread.

the equipment list is one table with info added to each tag #. The master parts list is another table.

i agree with normalization of my data base. probably something i need to start over with?

[June7]

The DLookup criteria is based on only matching "[Part # OEM]='" & [Engine Oil Filter #1].

The query with join does the same thing.

What happens when you try to attach file - error message, nothing?

[Eric2013]

June 7, is this what i would use : SELECT Equip1.*, Location FROM Equip1 RIGHT JOIN MasterPrts ON Equip1.[Engine Oil Filter #1]=MasterPrts.[Part # OEM]; to do the join between the 2 tables.?

I've attached a copy of the database i'm playing with. no upload error messages.

[June7]

Yes, the SQL you posted is what I suggested in post 5.

The query object does not have a join.

There is no reason to use the intermediate queries. Build query that joins tables.

You have established a link in Relationships between autonumber ID fields. This is not appropriate. These values are auto generated by Access and have to relationship to each other.

You have multiple similar name fields: [Engine Oil Filter #1], [Engine Oil Filter #2], [Part # OEM], [Part # Other]. This is not a normalized data structure and will cause issues.

Again, advise not to use spaces and symbols in naming.