Update records from a single record...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sheila D - 16 Nov 2005 16:24 GMT

I have a make table query which creates 3 records based on expenditure. All
individual records are allocated to 1 of 2 separate locations or identified
as a shared cost.

So I end up with SAY
1. Location 1 £50000
2. Location 2 £50000
3. Shared £10000

I need to update the Total Spend field for Location 1 and 2 records to
include 50% of shared record spend - not sure of best way to approach this,
any help woud be much appreciated. Not really a VB person so simple answers
if possible. Thanks

Sheila

Reply to this Message

[MVP] S.Clark - 16 Nov 2005 20:14 GMT

Update Location 1 with half of the shared value. Repeat for Location 2.
Delete the Shared record.

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

>I have a make table query which creates 3 records based on expenditure. All
> individual records are allocated to 1 of 2 separate locations or
[quoted text clipped - 14 lines]
>
> Sheila

Reply to this Message

Sheila D - 17 Nov 2005 09:58 GMT

Hi Steve

tx for your answer, that is what I want to do but I can't figure out HOW to
do, the shared value does not exist in the other records so I can't add to
the field value, can't figure out how to add the value from 1 record to the
value in another.

Sheila

> Update Location 1 with half of the shared value. Repeat for Location 2.
> Delete the Shared record.
[quoted text clipped - 17 lines]
> >
> > Sheila

Reply to this Message

[MVP] S.Clark - 18 Nov 2005 14:57 GMT

Ok. Sorry, but I made the assumption that those three records were in a
table together. Post your exact table structure and we'll try to brew up a
couple queries for you.

> H

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

i Steve

>
> tx for your answer, that is what I want to do but I can't figure out HOW
[quoted text clipped - 28 lines]
>> >
>> > Sheila

Reply to this Message

Sheila D - 21 Nov 2005 14:20 GMT

Hi Steve

This is a sample of what I end up with from Totals query:
Service    Cost Total    Cost_incl_Overhead
Training    20000
Web    20000
Overhead    10000

So I have 3 records and what I want to do is calculate the Cost incl
overhead by adding 50% of Overhead total in record 3 to Cost Total in
records 1 & 2 - i.e. 25000 each.

So the records are all in 1 table but I can't see how to update the values
with amounts that only exist in record 3 - am I missing something obvious
here?

Your help is much appreciated

Sheila

> Ok. Sorry, but I made the assumption that those three records were in a
> table together. Post your exact table structure and we'll try to brew up a
[quoted text clipped - 33 lines]
> >> >
> >> > Sheila

Reply to this Message

[MVP] S.Clark - 21 Nov 2005 23:50 GMT

Trying breaking this up into 2 or more queries. The problem being that you
have to compute the % that OH is to the others.

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

> Hi Steve
>
[quoted text clipped - 60 lines]
>> >> >
>> >> > Sheila

Reply to this Message