Solved

DMin refresher

Posted on 2013-01-31
6
284 Views
Last Modified: 2013-01-31
I am trying to look up the oldest order a member made to update the member table but need a refresher.

Here's what I have:
t: DMin("DeliveryDate","tblTranslationContractHistory","[tblTranslationContractHistory]![ContactID] = [tblTranslationContractMembers]![ContactID] ")

The sql error is can't find the name tblTranslationContractMembers.ContactID

Any ideas to fix my a lib syntax?
0
Comment
Question by:Shawn
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38841575
Can you give the full context of the query?
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 200 total points
ID: 38841592
winging it:

select [contactID],  DMin("DeliveryDate","tblTranslationContractHistory","[tblTranslationContractHistory]![ContactID] = ' " & a.[contactID] & "'") as t from [tblTranslationContractMembers] as a
0
 
LVL 1

Author Comment

by:Shawn
ID: 38841914
note quite.

Here is a query which works but it is a groupby query qhich doesn't work to update. I need the equivalent with dmin.

SELECT tblTranslationContractMembers.DateStart, tblTranslationContractMembers.ContactID, Min(tblTranslationContractHistory.DeliveryDate) AS MinOfDeliveryDate
FROM tblTranslationContractHistory INNER JOIN tblTranslationContractMembers ON tblTranslationContractHistory.ContactID = tblTranslationContractMembers.ContactID
GROUP BY tblTranslationContractMembers.DateStart, tblTranslationContractMembers.ContactID;
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 26

Expert Comment

by:jerryb30
ID: 38841922
Just to save me some time, can you post a sample db with some dummy data?
And, maybe what you are trying to update.
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 300 total points
ID: 38842401
Give this a whirl ...

SELECT tblTranslationContractMembers.DateStart
      , tblTranslationContractMembers.ContactID
      , vtblFirstDeliveries.FirstDelivery
FROM tblTranslationContractMembers
     LEFT JOIN
       (SELECT ContactId, Min(DeliveryDate) As FirstDelivery
        FROM tblTranslationContractHistory
        GROUP BY ContactId) As vtblFirstDeliveries 
     ON tblTranslationContractMembers.ContactId = vtblFirstDeliveries.ContactId

Open in new window

0
 
LVL 1

Author Comment

by:Shawn
ID: 38842528
wow  datAdrenaline, you knocked it out of the park. Nice.

I also got this to work but I like your query.

DMin("DeliveryDate";"tblTranslationContractHistory";"ContactID=" & [contactID])
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

914 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

16 Experts available now in Live!

Get 1:1 Help Now