UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Query Adds A Certificate Number To Another Unrelated Table, Access 2016    
 
   
Oblio
post Feb 26 2018, 11:48 AM
Post#1



Posts: 205
Joined: 5-February 15




I have two tables. t_MCRs ( is a scanned form ) and t_Certificates ( a set of alpha-numeric certificate numbers )

t_Certificates:

Certificates_ID, autonumber, Long integer
MCRs_DE_ID, Short Text ( This is the certificate field I want to add to t_MCRS, in the empty field named MCRS_DE_ID )
Certificate_Assigned, Boolean, Yes/No, ( I want this to be updated to Yes once a certificate has been assigned to t_MCRs )

t_MCRs

MCRs_ID, number, integer
MCRs_DE_ID, short text

In the update query, am I able to update t_MCRs where MCRs_DE_I'D in t_MCRs is Null....

So, if t_MCRs has 5 records where MCRs_DE_ID is empty, can the update query apply the next set of 5 unused certificates, and then mark those as used?

Thanks,

BIll

Go to the top of the page
 
theDBguy
post Feb 26 2018, 11:50 AM
Post#2


Access Wiki and Forums Moderator
Posts: 72,444
Joined: 19-June 07
From: SunnySandyEggo


Hi Bill,

I believe an UPDATE query can only update one table at a time, but I could be wrong since I haven't tried it before.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Feb 26 2018, 01:52 PM
Post#3


UA Admin
Posts: 32,835
Joined: 20-June 02
From: Newcastle, WA


I concur.

This might take a Function or Sub in VBA that runs each of your update queries in turn.

--------------------
Go to the top of the page
 
Oblio
post Feb 26 2018, 01:52 PM
Post#4



Posts: 205
Joined: 5-February 15



OK, thanks DBGUY. .. any ideas on how you might accomplish this.....using VBA for instance ?

Thanks,

Bill
Go to the top of the page
 
zaxbat
post Feb 26 2018, 02:31 PM
Post#5



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Are you basically just trying to earmark these certificates and reserve them for use with said records? If so, you might put a status field in for the certificate to mark it as available reserved used etc. and when choosing next available certificate number take the first one with 'available' status. Just an idea....

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Oblio
post Feb 27 2018, 10:01 AM
Post#6



Posts: 205
Joined: 5-February 15



Yes, we need to add a certificate number which is used by a partner company as their reference...

I just am not sure how to apply something that is not related in a meaningful way...

SELECT * FROM t_Certificates WHERE certificate_Assigned = No and t_MCRs!MCRs_DE_ID is Null is sort of what is going around in my head but I really do not know how to write the function...any samples or guidance you can provide would be very helpful...

It's a rush job...I have a week to work it out... so any help would be great !!!

Thanks,

Bill
Go to the top of the page
 
Oblio
post Feb 27 2018, 10:28 AM
Post#7



Posts: 205
Joined: 5-February 15



Hi GroverParkGeorge,

Are you aware of anything out there resembling this type of scenario ?
Go to the top of the page
 
Oblio
post Feb 27 2018, 02:52 PM
Post#8



Posts: 205
Joined: 5-February 15



I have tried the following SQL in an update query, however, it only copies the first certificate to each of the records in t_MCRs...any idea on doing this in an update query so that each record in t_MCRs gets a unique certificate number?

Here is the update query:

UPDATE t_Certificates, t_MCRs SET t_MCRs.MCRs_DE_ID = [t_Certificates]![MCRs_DE_ID]
WHERE (((t_MCRs.FName) Like "01.pdf"));

The "Like 01.pdf" limits the number of certificates required to the number of empty certificates in t_MCRs.

But, it only copies the very first Certificate to each record in t_MCRs...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 02:23 AM