How to Input data from one field base off another

[SusanK4305]

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

[Delerna]

Do you mean

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


or

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?

[SusanK4305]

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. :)

[Delerna]

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

SELECT TableA.Field1,
   TableA.field2,
   TableA.field3,
   (   select Field3 
       FROM TableB 
       WHERE tableB.field1=TableA.Field1
    ) as UpdateValue
FROM TableA
Where TableA.Field1 in
(  select tableB.Field1 
   from tableB 
   where tableB.Field2="out"
)

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

UPDATE TableA
Set TableA.Field3=(select Field3 FROM TableB WHERE tableB.field1=TableA.Field1)
and TableA.Field1 in
(  select tableB.Field1 
   from tableB 
   where tableB.Field2="out"
)

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

[SusanK4305]

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?

[SusanK4305]

Also if I am making 2 Queries can I make them via Wizard?

[Delerna]

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

[Delerna]

A select query is a query that selects data from a table or another query.
It has the form

SELECT FieldsToSelect
FROM TableName   (or QueryName)
WHERE SelectionCriteria
ORDER BY FieldsToOrderBy

[SusanK4305]

No I am using 2000 :( gov cheap.

[Delerna]

That should be very similar, are you OK to try it out?

[SusanK4305]

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!

[Delerna]

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

SELECT TableA.Field1, 
   TableA.field2, 
   TableA.field3,
   TableB.Field3  
FROM TableA 
JOIN TABLEB on TableA.Field1=TableB.Field1
where tableB.Field2="out"

now the query coverted to an update query

UPDATE TableA 
SET TableA.field3=TableB.Field3  
FROM TableA 
JOIN TABLEB on TableA.Field1=TableB.Field1
where tableB.Field2="out"

You can try using the wizard with these.
Or simply copy and paste them as I described above

[SusanK4305]

ok I tryed the simpler part
SELECT TableA.Field1,
TableA.field2,
TableA.field3,
TableB.Field3
FROM TableA
JOIN TABLEB on TableA.Field1=T ableB.Field1
where tableB.Field2=" out"
However the "JOIN" keeps coming up as an Error

[SusanK4305]

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"

[SusanK4305]

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.