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
42 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
zlib is a free compression library (a DLL) on which the popular gzip utility is built.  In this article, we'll see how to use the zlib functions to compress and decompress data in memory; that is, without needing to use a temporary file.  We'll be c…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

815 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

10 Experts available now in Live!

Get 1:1 Help Now