?
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
?
60 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes how to programmatically preset the "Pages per Sheet" option that's available with most printer drivers.   This setting lets you do "n-Up" printing, where two, four, or more pages are printed on each sheet of paper. If your …
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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

649 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