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
37 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:CraigYellick
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:
CraigYellick earned 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article shows a few slightly more advanced techniques for Windows 7 gadget programming, including how to save and restore user settings for your gadget and how to populate the "details" panel that is displayed in the Windows 7 gadget gallery.  …
If you have ever found yourself doing a repetitive action with the mouse and keyboard, and if you have even a little programming experience, there is a good chance that you can use a text editor to whip together a sort of macro to automate the proce…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now