update query from one table to another (1 Viewer)

[shutzy]

i have 2 tables that hold similar data. in one table tblMultiSchedule i have a few blank columns. i want to update each column(ActualCost*) with the correct data which is stored in tblOrdersItems. the corresponding fields in each tbl are

tblMultiSchedule.OrderItemID1
tblOrdersItems.OrdersItemsID

ive been trying all day to have an update query to input the actual cost data into the tblMultiSchedule.

can anyone help with this.

i know the rules regarding duplicate data but this is the only way i can do this.

thank you

 

[vbaInet]

Of course you know shutzy that I'm going to question your tables that hold similar data Do they both have the same fields? And what's the idea behind that?

i know the rules regarding duplicate data but this is the only way i can do this.

And it's probably not the only way it can be done. It might be better to address this issue instead.

What problems are you having with the UPDATE query anyway?

 

[plog]

Allow me to pile on with the skepticism:

i know the rules regarding duplicate data but this is the only way i can do this.

Even if we give you a pass on that, you're breaking a few other rules:

1. tblOrdersItems.StartDate & tblOrdersItems.StartTime: there is no reason to have 2 seperate fields for this data. It's called a Date/Time field for a reason--it can hold both pieces of those information. These 2 fields need to be 1.

2. tblMultiSchedule has numerated field names. When you start suffixing fields with numbers (Item1, Item2, Client1, Client2, etc.), its most likely time for a new table. Instead of 5 different sets of Item/Client/Status/ActualCost/OrderItemID fields in tblMultiSchedule, you need to have a new table.


The issue you are currently posting about is just a symptom of a bigger problem. You need to structure your database properly or you will continue to need help with hacks to get it to work like you want.

 

[shutzy]

ok ok. the reason they have been duplicated is for a diary system.

i have frmDiary and frmDiarySub1. sub1 holds 5 columns which each relate to one set of details in tblMultiSchedule.

i had this built for me and cant for the life of me figure out how he gets data into the txt boxes on the form except through tblMultiSchedule. so i want to add a sum to each individual column for the days takings which are summed from the actual costs for each set(column).

i would love to be able to post my database so you guys can see it. the diary view is fantastic with a few tricks. but it is tto large for the maximum upload. if you would like i can upload it to my web server for you to download. its about 20mb and only in 2010.

i think i have left alot of things out but if i was to tell them all i would be sat here for hours typing telling you how to use it.

i managed to do the query. turns out i was doing it backwards. i was updating tblOrdersItems not tblMultiSchedule. i always get confused with update queries.

thanks for listening!

 

[vbaInet]

i would love to be able to post my database so you guys can see it. the diary view is fantastic with a few tricks. but it is tto large for the maximum upload. if you would like i can upload it to my web server for you to download. its about 20mb and only in 2010.

No it's ok, not unless plog wants to see it

Good to hear you got it fixed.