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
49 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: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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

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…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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