469,128 Members | 1,518 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

How to Input data from one field base off another

How do I get somthing like the following idea in my tables?

In Table B "If [field 2]= "out" then [Field 3] will be input in to Table A [field 3]"

Table A:
Field 1 = ID
Field 2 = Name
Field 3 = Out Date (txt field w/ an inputmask)

Table B:
Field 1 = ID (there is a relationship between A and B w this field)
Field 2 = Type (drop down of choices from a Query)
Field 3 = Date
Sep 20 '10 #1
42 2959
1,134 Expert 1GB
Do you mean

How to update tablea.field3 from tableb.field3
where tableb.field2="out"


how to write a query that selects tableB.field3 when tablea.field2="out"
otherwise it selects tableb.field2

I guess my quetion is
Are you wanting an
update query or a select query?
Sep 21 '10 #2
How to update tableA.field3 from tableB.field3
where tableB.field2="out"

but this must be done automatic and there is a second part as well however one step at a time. :)
Sep 21 '10 #3
1,134 Expert 1GB
You should always start an update query with a select query so that you can be sure that what will be updated is what you want updated. Doing an update without doing that first is dangerous and I have been cought a couple of times. Once updated you can't go back (there is no undo) ..... unless you have a backup
Expand|Select|Wrap|Line Numbers
  1. SELECT TableA.Field1,
  2.    TableA.field2,
  3.    TableA.field3,
  4.    (   select Field3 
  5.        FROM TableB 
  6.        WHERE tableB.field1=TableA.Field1
  7.     ) as UpdateValue
  8. FROM TableA
  9. Where TableA.Field1 in
  10. (  select tableB.Field1 
  11.    from tableB 
  12.    where tableB.Field2="out"
  13. )
Once you are sure the correct records are going to be updated with the correct value
then you convert the select query to an update query

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableA
  2. Set TableA.Field3=(select Field3 FROM TableB WHERE tableB.field1=TableA.Field1)
  3. and TableA.Field1 in
  4. (  select tableB.Field1 
  5.    from tableB 
  6.    where tableB.Field2="out"
  7. )

Thats untested and out of my head.
There's probably a better way so I will research it a little and post back.
If someone else has a better or simpler way feel free :)

In the meantime, go ahead and try it out
Sep 21 '10 #4
Umm... what is a select Query and how/ where do I add these codes in on my form. I am guessing that I should build 2 Queries "Select Query for Table A" and a "Update Query for Table A" then create my Form from that...rt?
Sep 21 '10 #5
Also if I am making 2 Queries can I make them via Wizard?
Sep 21 '10 #6
1,134 Expert 1GB
Oh Oh, this could be fun :)

No you cant use the wizard for these particular queries. They are outside the wizards capabilities.

In Access you have several sections.
Tables Queries Forms Reports Modules etc.

1) Goto the QUERIES section (the correct name is escaping my memory at the moment)

2) Create a new query and choose design view

3)Close the show table window but keep the query designer window open

4)In the toolbar at the top there should be a dropdown with Design View,SQL View etc
choose "SQL View". This is where you can write queries without the assistance of the wizard

5) In the window that pops up you need to paste the first query in my post.
This is a select query

Close and name this query and then you should be able to double click it and it will run and display the records that the query is selecting. You need to examine these to ensure that the records that will be updated with the UpdateValue are the records you want updated

Also, I am assuming that the table and field names that you gave are the real table and field names. If not you will need to change them the suit the actual names

The above is using access 2003
If you are not using 2003 then it will be slightly different to what I describe
Sep 21 '10 #7
1,134 Expert 1GB
A select query is a query that selects data from a table or another query.
It has the form

Expand|Select|Wrap|Line Numbers
  1. SELECT FieldsToSelect
  2. FROM TableName   (or QueryName)
  3. WHERE SelectionCriteria
  4. ORDER BY FieldsToOrderBy
Sep 21 '10 #8
No I am using 2000 :( gov cheap.
Sep 21 '10 #9
1,134 Expert 1GB
That should be very similar, are you OK to try it out?
Sep 21 '10 #10
Can try rt now but I will as soon as I get to work. I understand the instructions and it makes things clear y it didn't work before. I will keep u posted on the out come. Thank you!
Sep 21 '10 #11
1,134 Expert 1GB
Did some thinking and a few trials
Don't worry about me...it must be time to go home.

Here is a much simpler verion.

First the query to check that the correct records will get updated
Expand|Select|Wrap|Line Numbers
  1. SELECT TableA.Field1, 
  2.    TableA.field2, 
  3.    TableA.field3,
  4.    TableB.Field3  
  5. FROM TableA 
  6. JOIN TABLEB on TableA.Field1=TableB.Field1
  7. where tableB.Field2="out"

now the query coverted to an update query
Expand|Select|Wrap|Line Numbers
  1. UPDATE TableA 
  2. SET TableA.field3=TableB.Field3  
  3. FROM TableA 
  4. JOIN TABLEB on TableA.Field1=TableB.Field1
  5. where tableB.Field2="out"

You can try using the wizard with these.
Or simply copy and paste them as I described above
Sep 21 '10 #12
ok I tryed the simpler part
SELECT TableA.Field1,
JOIN TABLEB on TableA.Field1=TableB.Field1
where tableB.Field2="out"
However the "JOIN" keeps coming up as an Error
Sep 21 '10 #13
I have now tried the first select Query and I can get it to however when I go to open it I get an Error Message
"Date Type mismatch in criteria exspression"
Sep 21 '10 #14
I think the issue has to do w/ Table A and there date really being a txt field, so I am haveing every one get out of it so I can change it.
Sep 21 '10 #15
Now if this works I need to take what we have done and make it for other fields ...same way.
Table A:
Field 1 = ID (SSN)
Field 2 = Name (Please note I didn't use this field in mine)
Field 3 = Out Date (this is changeing to adate/time feild @ 1500 CST)
Field 4 = Delayed Date (Date/time field)
**and so on

Table B:
Field 1 = ID (there is a relationship between A and B w this field- SSN)
Field 2 = Type (drop down of choices from a Query)
Field 3 = Date

What I have is the out date being transfered but I need to add on. If [Table B].[Type]= "Delay" then update [Table A].[Delay Date] w/ [Table B].[Date]
Sep 21 '10 #16
ok I have corrected the data type so now all dates are Date/time fields, but I get a new Error message.
***At most one record can be returned by this subquery***
Sep 21 '10 #17
Here is what I have:
SELECT [tblDallas PSU Database].[Social Security Number], [tblDallas PSU Database].[To EOD Date], (select [Action Date]
FROM [tblAction Log]
WHERE [tblAction Log].[Social Security Number]=[tblDallas PSU Database].[Social Security Number]) AS UpdateValue
FROM [tblDallas PSU Database]
WHERE [tblDallas PSU Database].[Social Security Number] in
(Select [tblAction Log].[Social Security Number]
from [tblAction Log]
where [tblAction Log].[Action Type] = "Sent to EOD" )

Table A:
Field 1 = ID (SSN)
Field 2 = Name (Please note I didn't use this field in mine)
Field 3 = Out Date (a.k.a To EOD Date)
**and so on

Table B:
Field 1 = ID (there is a relationship between A and B w this field- SSN)
Field 2 = Type (drop down of choices from a Query; a.k.a. Action Type)
Field 3 = Date (a.k.a. Action Date)
Sep 21 '10 #18
oops Table A (a.k.a. tblDallas PSU Database)and Table B (a.k.a. tblAction Log)
Sep 21 '10 #19
1,134 Expert 1GB
That error means that the subquery being used for the Update value is returning more than 1 record.

You need to think about what you need to do and ensure that the subquery can only ever return 1 record for each record in the main query.

By the way, use code tags around any code you post, and make it readable

your query in code tags and made readable
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblDallas PSU Database].[Social Security Number], 
  2.    [tblDallas PSU Database].[To EOD Date],
  3.    (   select [Action Date]
  4.        FROM [tblAction Log] 
  5.        WHERE [tblAction Log].[Social Security Number]=[tblDallas PSU Database].[Social Security Number]
  6.    ) AS UpdateValue
  7. FROM [tblDallas PSU Database]
  8. WHERE [tblDallas PSU Database].[Social Security Number] in 
  9. (   Select [tblAction Log].[Social Security Number] 
  10.     from [tblAction Log] 
  11.     where [tblAction Log].[Action Type] = "Sent to EOD" 
  12. )
Sep 21 '10 #20
1,134 Expert 1GB
so this part
Expand|Select|Wrap|Line Numbers
  1.    (   select [Action Date]
  2.        FROM [tblAction Log] 
  3.        WHERE [tblAction Log].[Social Security Number]=[tblDallas PSU Database].[Social Security Number]
  4.    ) AS UpdateValue
is returning more than one record for at least 1 particular [Social Security Number]

Now that I can see your real table and field names I can see that there would be many actions recorded.

So, you need to decide which date from the actios log table you want to update into the tblDallas PSU Database

I will assume you want the date of the latest action, in which case changing the subquery to this should work for you
Expand|Select|Wrap|Line Numbers
  1.    (   select max([Action Date])
  2.        FROM [tblAction Log] 
  3.        WHERE [tblAction Log].[Social Security Number]=[tblDallas PSU Database].[Social Security Number]
  4.    ) AS UpdateValue
Sep 21 '10 #21
Yes! :) that worked.
so just to check my query reads as shown
SSN : 000-00-0009 and 000-00-0010
To EOD Date: Blank and Blank
UpDate Value: 9/14/2010 (this is the same as the action log) and 9/26/2010 (this is correct)

So now I put in the nexted part to link it.... rt?
Sep 22 '10 #22
1,134 Expert 1GB
Yep if your happy the select query is using the correct values and its not going to update any records that it shouldn't then you can convert it to an update query.

I would probaly be a good idea to get a backup of thae table before the update.....just in case.

By the way, these precautions are only necessary while you develop the query. Once you know it works correctly then the precautions are not needed
Sep 22 '10 #23
Ok next question... I have to do the same thing to 4 more. I have done the selection to all and they look good. My question is ... do I have to have all differant queries or can I put them all in one?
Sep 24 '10 #24
I have done the Update Queries. However I get an Error message.
This is what I put in (I used SQL to do this)

UPDATE [tblDallas PSU Database] SET [tblDallas PSU Database].[To EOD Date] = (select [Action Date] FROM [tblAction Log] WHERE [tblAction Log].[Social Security Number]= [tblDallas PSU Database].[Social Security Number]) And [tblDallas PSU Database].[Social Security Number] In (select [tblAction Log].[Social Security Number]
FROM [tblAction Log]
WHERE [tblAction Log].[Action Type]="Sent to EOD");

On opening the query I get an Error Message: Operations must use an updateable query.
Sep 24 '10 #25
I am wondering if it has anything to do w/ the fact there are many ppl w/ the DB open and that is y it won't update. Is this a pos. reason? If so, how can I fix this to update as soon as the Action Log record is saved?
Sep 24 '10 #26
1,134 Expert 1GB
Can't see anything obvious in the query

Can you run the update at a time when no-one is using the database to confirm that theory
Sep 27 '10 #27
No because ppl leave it open over night. Is there a way I can over ride it... if that is the reason or is there another way to do it.
Sep 27 '10 #28
255 100+
Sadly to say no...unless you hack to their computer and shut the access. Access kept individual changes from each different computers, this is used to prevent database corruptions when ppl doing data transaction simultaneously.

Why not just tell them to shut the access down before they leave?
Sep 28 '10 #29
332 100+
Hi Susan
You could have a hidden form that has a timer event that looks for inactivity. When there is inactivity, the db will close. The form must autoexec at startup. It is not fullproof, but it might help.

But the fact that you get the message: "Operations must use an updateable query" does not sound to me like this is the problem.
Sep 28 '10 #30
Ok sounds like a good idea to get that hiden code. I think my boss would like that.
Ref. to the error message: What do you think it is?
Sep 28 '10 #31
I am not sure if an Append Query would work in some way. Do you think it might?
Sep 30 '10 #32
255 100+
I just read your query, there is a problem within the SET area, I'll paste this part here so no one needs to scroll back up.
Expand|Select|Wrap|Line Numbers
  1. SET 
  2. [tblDallas PSU Database].[To EOD Date] = 
  3.    (select [Action Date] 
  4.     FROM [tblAction Log] 
  5.     WHERE [tblAction Log].[Social Security Number]= [tblDallas PSU Database].[Social Security Number]) 
  6. And [tblDallas PSU Database].[Social Security Number] 
  7.    ---->In<---- (select [tblAction Log].[Social Security Number] 
  8.       FROM [tblAction Log] 
  9.       WHERE [tblAction Log].[Action Type]="Sent to EOD");
Notice the bold words at the end, this is where error comes from. The SET is only to change the target field into something else (must use =) instead of getting the record (the IN you are using).

Therefore you have to make sure the target you want to update and the sub-query is matching an one-to-one condition, not one-to-many.
Sep 30 '10 #33
1,134 Expert 1GB
Actually, you just highlighted something for me Colintis.

Back in Post 17 you had a
***At most one record can be returned by this subquery***

In Post 20 I explained what the error meant
and in post 21 I showed you what you needed to do
Expand|Select|Wrap|Line Numbers
  1. ...
  2. select max([Action Date]) 
  3. ...

In post 22 you didn't say exactly what you did but whatever it was, you said it worked.

Looking at your code in the subquery you haven't repeated whatever it was you did when you converted it to an update query.

The bold underlined part is not in your subquery
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblDallas PSU Database] 
  2.    SET [tblDallas PSU Database].[To EOD Date] = 
  3. (   select MAX( [Action Date])
  4.     FROM [tblAction Log] 
  5.     WHERE [tblAction Log].[Social Security Number]= [tblDallas PSU Database].[Social Security Number]) 
  6.       And [tblDallas PSU Database].[Social Security Number] In 
  7. (   select [tblAction Log].[Social Security Number] 
  8.     FROM [tblAction Log] 
  9.     WHERE [tblAction Log].[Action Type]="Sent to EOD"
  10. );
Oct 1 '10 #34
1,134 Expert 1GB
And now when checking out the query I notice that it has become a bit of a mess

try this
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblDallas PSU Database] 
  2. SET [tblDallas PSU Database].[To EOD Date] = 
  3.       (    select MAX( [Action Date])
  4.            FROM [tblAction Log] 
  5.            WHERE [tblAction Log].[Social Security Number]
  6.                = [tblDallas PSU Database].[Social Security Number]
  7.              AND [tblAction Log].[Action Type]="Sent to EOD"
  8.       ) 
  9. WHERE [tblDallas PSU Database].[Social Security Number] In 
  10. (   select [tblAction Log].[Social Security Number] 
  11.     FROM [tblAction Log] 
  12.     WHERE [tblAction Log].[Action Type]="Sent to EOD"
  13. );
I have bolded the changes I made

There are actually 2 subqueries here

The top one selects the value to update for each record
The most recent action date for the [Social Security Number]

the bottom one controls which records will be updated
Only update the records where there is a record in [tblAction Log] where [Action Type]="Sent to EOD"

This ensures that no record will be updated with a null value

Changes are bold and underlined
Oct 1 '10 #35
1,134 Expert 1GB
Its hard for me to tell because I don't have the full story but you may not even need the second subquery.

But it probably wouldn't hurt to have it there any way.

Please please please keep a backup of your data prior to doing the update, especially considering that you are relatively inexperienced.
As I said earlier, there is no undo after an update query unless you have a backup
Oct 1 '10 #36
255 100+
Delerna, I think we should putting underline instead of bolding the parts, as i can't even see the query is bold or not in the code tag...
Oct 1 '10 #37
1,134 Expert 1GB
changes are bold and underlined
Oct 1 '10 #38
1,134 Expert 1GB
Actually, seeing as it's a live database
The best thing to do would be to import the table and its data into another database and develop the update query there.

Susan....have I instilled a healthy fear of developing and running unproven update queries on live data in you yet ? :)

I hope so, it is dangerous to become blaze about developing update queries on the fly with live data.;

I cringe with fear everytime I have to do it.
I don't believe you can be too cautious here.
Prove it works correctly before doing it on the live data
Oct 1 '10 #39
Ok I will copy both and make a tester location. :)
FYI: The select query looked to be working I just had issues w/ the update part. I will try the new SQL and let ya'll know. Thank you so much for ya'lls help.
Oct 4 '10 #40
Ok I have added the SQL you told me to and received the same error message. However this time I copied both database and set them a side so only I was in them. What am I doing wrong?

For the update Query all I did was make a blank query the opened it to the SQL view, pasted the code you gave me and ran it. The received the same Error message.

UPDATE [tblDallas PSU Database]
SET [tblDallas PSU Database].[To EOD Date] =
( select MAX( [Action Date])
FROM [tblAction Log]
WHERE [tblAction Log].[Social Security Number]
= [tblDallas PSU Database].[Social Security Number]
AND [tblAction Log].[Action Type]="Sent to EOD"
WHERE [tblDallas PSU Database].[Social Security Number] In
( select [tblAction Log].[Social Security Number]
FROM [tblAction Log]
WHERE [tblAction Log].[Action Type]="Sent to EOD"
Oct 4 '10 #41
FYI: The answer came from another post that I thought he gave up on. I posted it here just in case another may need it.

answered by NeoPa
If there are multiple possible versions of these records then you will need to decide how to determine which to use.
This SQL should work for taking the last date found in the sequence of the records as they come. NB. This is not the same as coming in date sequence necessarily. If there is only one of each then you shouldn't have the problem. Try it out and see how you get on with it :
Expand|Select|Wrap|Line Numbers UPDATE [Table A] AS tA
[Table B] AS tB

SET tA.[Intake Delay Date] = IIf(tB.[Action Type] = 'Delayed'
, tB.[Action Date]
, tA.[Intake Delay Date])
, tA.[Intake Completed] = IIf(tB.[Action Type] = 'Done'
, tB.[Action Date]
, tA.[Intake Completed])

WHERE (tB.[Action Type] In('Delayed', 'Done'))

Now I am just trying to get the Query to run the update every 5 sec. Do ya'll know how to do that?
Oct 4 '10 #42
1,134 Expert 1GB
Similar to post 12

Is there a form that is open all the time.
You could use the forms timer event to run the query.
Haven't done any access programming in a while.
Someone will come up with a better way

From the sound of things it is now becoming apparent that it might have been easier if the form where the users enter the action log should be automatically updating the tblDallas PSU Database tables date fields at the same time
Oct 5 '10 #43

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by stathis gotsis | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.