General Query Question Please Help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All I need to know is..Can you take 5 different tables and update certain
fields from those tables into a master table? If so can it be done with an
Update query? I would think you would just put this expression in the "update
to" line..[Dev_dbo_TblProduct].[ProductLine] if your query or fields were
coming from several different tables or fileds. Am I correct on this? Thank
you in advance
 
That's somewhat tricky...b/c unless the data from 1 table has a 1 to 1
relationship with data from the 4 other tables, it gets difficult. I would
recommend using queries for this type of thing over creating another table.
But even if you made a query, you would have to have one of your tables be
the "main" table, and then have the other tables show relevant data, if
applicable. This is my first time replying to anything, so I hope this was
not a complete waste of your time...
 
The simple answer to your question is Yes, it can be done, and an
Update Query is one method of doing this.

You are also correct in your thinking, you would put
.[Object]
in the "Update To:" field in your query. An easy way to do this is to
right click on the "Update To:" field and click build. Then drill down
to the field you want to use to update your master table. Just make
sure you display the 5 different tables along with your master table on
the top half of the query builder.

One question that comes to mind is: Do records already exist in the
master table that you need to update? If not, I think you would need to
be using an append query if you are trying to add new records based off
data in other tables.

If you are updating existing records with fields from child tables make
sure you add in criteria to filter only the records you want to
updated. You can use the same build function to create the criteria as
described for the "Update To:" field above.
 
Thank you very much. How would I update a fields or fields that I changed
with an expression? Such as
EAndOFlag: IIf(dbo_tblProductversion.EAndOFlag=-1,"Y","N")

I would like to update my master table and I had to do the above expression
because the target table has a text type datatype. Access is not allowing me
to add the table where the above field came from. I get an error message.
Thanks again
 
Back
Top