[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DLookUp  Has anybody got a clue how I post this to MSAccess group. Changes for the sake of it again?

Posted on 2016-08-18
5
Medium Priority
?
64 Views
Last Modified: 2016-08-19
I have a query that lists details of House Doors required by a builder. He orders Height Width Thickness Brand  etc. but there are 4 sizes of each brand available in the products table, which is the correct size will depend on Width Height Thickness and Brand required from  the product table. Can I get the query or another one to look through the products table and select the correct "Type" to suit the criteria from the orders table.

Example. Order Query
Width 1200 Height 2040 Thickness 44 Brand X Type ?

Product Table
Width 1000 Height 1981 Thickness 44 Brand X Type A
Width 900   Height 2440 Thickness 44 Brand X Type B
Width 1200 Height 2440 Thickness 44 Brand X Type C
Width 1200 Height 2040 Thickness 44 Brand X Type D

All I need is the Type A B C or D

I know that normally there would be a link between the tables and This would be easy but there are reasons why I don't want to do this.

I have read this 4 times I think it makes sense.
0
Comment
Question by:DatabaseDek
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 41761515
That's the job of a WHERE clause in any query.

select * from ProductTable
where Width = 1200 and Height = 2040 
and Thickness = 44 and Brand ='X'

Open in new window


If this has to be done in a DLookup it would look like this:

dlookup("Type","ProductTable","Width = 1200 and Height = 2040 and Thickness = 44 and Brand ='X'")

Open in new window

0
 

Author Comment

by:DatabaseDek
ID: 41761556
Thank you.

But I need each record in Order table to get Type for each record. So criteria changes for each record
0
 
LVL 11

Accepted Solution

by:
Craig Yellick earned 2000 total points
ID: 41761564
A join would do the job across multiple records:
select p.Type, o.* 
from ProductTable as p
inner join OrderTable as o on 
    o.Width = p.Width
and o.Height = p.Height
and o.Thickness = p.Thickness
and o.Brand = p.Brand

Open in new window

0
 

Author Comment

by:DatabaseDek
ID: 41761644
I will try that tomorrow. Thank you.
0
 

Author Closing Comment

by:DatabaseDek
ID: 41763250
Thank you. Worked fine.

If you have any sway with the management of this in this EE group any chance we could switch off this login and security stuff. What could I possibly be asking that needs to be secure???? Can we at least have a choice?
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial is about how to put some of your C++ program's functionality into a standard DLL, and how to make working with the EXE and the DLL simple and seamless.   We'll be using Microsoft Visual Studio 2008 and we will cut out the noise; that i…
This article surveys and compares options for encoding and decoding base64 data.  It includes source code in C++ as well as examples of how to use standard Windows API functions for these tasks. We'll look at the algorithms — how encoding and decodi…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question