By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,012 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Inserting product classification using Dlookup

P: 25
i'm having trouble with Dlookup.I'm trying to insert a Dlookup statement in a update query, so that the user need not upate each record manually.

Supplier Catergory
sSupplierName
sCatergory

Transaction
tSupplierName
tAmount
tCatergory

What i need is to match the supplier name from transaction to the supplier name in the supplier catergory to retrieve the catergory in which the supplier falls in.

How should my Dlookup statement be like? Should i have a same name for the supplier name in the different tables?

I have tried
DLookUp("[sCatergory]","[Supplier Catergory]","[tSupplierName]=" & "[sSupplierName]")
Sep 4 '07 #1
Share this Question
Share on Google+
2 Replies


Jim Doherty
Expert 100+
P: 897
i'm having trouble with Dlookup.I'm trying to insert a Dlookup statement in a update query, so that the user need not upate each record manually.

Supplier Catergory
sSupplierName
sCatergory

Transaction
tSupplierName
tAmount
tCatergory

What i need is to match the supplier name from transaction to the supplier name in the supplier catergory to retrieve the catergory in which the supplier falls in.

How should my Dlookup statement be like? Should i have a same name for the supplier name in the different tables?

I have tried
DLookUp("[sCatergory]","[Supplier Catergory]","[tSupplierName]=" & "[sSupplierName]")
If I am reading you right and you are setting this up as a new database (ie you have not inherited an age old database full of data and find yourself having to do something with it) and if your tables are as simple as that, and if each supplier is unique on the one side (sSupplier table) you should really have the supplier referenced by a long integer (autonumber) as the primary key and use a long integer in the transaction table as the foreign key to reference the suppliers transactions.

You should then define a relationship between the two invoke referential integrity and the cascade update delete options available at which point manual updates become unneccesary using Access Autolookup feature.

Using this method you have no need then to have the suppliers name in the transactions table merely the number referencing them together with the any other fields necessary to complete the transaction record.
.
Hope this helps if not then I obviously read you wrong?

Jim
Sep 4 '07 #2

P: 47
Try this (I'm assuming sSupplierName is a Text field)

Expand|Select|Wrap|Line Numbers
  1. DLookUp("[sCatergory]","[Supplier Catergory]","[tSupplierName]='" & [sSupplierName] & "'")
Sep 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.