Update query to sum field from another table

[Guest]

Hello, I need to update a field to the sum of another field in another table.
for example

I tried to something like UPDATE [PHYSICIAN GROUP] INNER JOIN [PHYSICIAN
RATE INFORMATION] ON [PHYSICIAN GROUP].[GROUP NUMBER] = [PHYSICIAN RATE
INFORMATION].[GROUP NUMBER] SET
[physician group].[gaf] = sum([physician rate information].[adfee] as
sumofadfee;

I basically want to sum based on group number in physician rate information
and assign this value to the group number in physician group.

any help would be greatly appreciated.

Thanks and have a nice day.

 

[Jeff Boyce]

Why update? Why store "sums"? Won't they change after every change to the
detail records?

Instead, consider just using a totals query to calculate the sums.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Yes, you are right because I will subtract a percentage of the adfee. How
do I create this totals query.

I will need this value to use in an exisiting update query such as
SET [GROUP TAX INFO].[1ST PAYMT SURPLUS] = IIf([does fet apply]=-1,([group
accounts receivable].[DEBIT1]/1.065)*0.025,([group accounts
receivable].[debit1])*0.025)

I will need to subtract this value from debit1 (which is a payment) before
dividing by 1.065.

I really appreciate your quick response and thanks in advance.

Why update? Why store "sums"? Won't they change after every change to the
detail records?

Instead, consider just using a totals query to calculate the sums.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello, I need to update a field to the sum of another field in another table.
for example

I tried to something like UPDATE [PHYSICIAN GROUP] INNER JOIN [PHYSICIAN
RATE INFORMATION] ON [PHYSICIAN GROUP].[GROUP NUMBER] = [PHYSICIAN RATE
INFORMATION].[GROUP NUMBER] SET
[physician group].[gaf] = sum([physician rate information].[adfee] as
sumofadfee;

I basically want to sum based on group number in physician rate information
and assign this value to the group number in physician group.

any help would be greatly appreciated.

Thanks and have a nice day.

 

[Guest]

If it helps any I know how to determine the amount to subtract

group ad fee 360.86 290.67 220.49 150.30 0
group net premium 9509 7659.5 5810 3960.5 0
af sl fet
payment 1 1849.5 70.19 41.77 66.83
payment 2 1849.5 70.19 41.77 66.83
payment 3 1849.5 70.19 41.77 66.83
payment 4 3960.5 150.30 89.44 143.11

360.86 214.75 343.59
i need to subtract af from group ad fee before I can determine sl and fet.
Each time a payment( this is an arbitrary number) is made, group ad fee is
reduced by af and group net premium is reduced by each payment.

in summary each payment is reduced by af and then the sl and fet are
determined.

Yes, you are right because I will subtract a percentage of the adfee. How
do I create this totals query.

I will need this value to use in an exisiting update query such as
SET [GROUP TAX INFO].[1ST PAYMT SURPLUS] = IIf([does fet apply]=-1,([group
accounts receivable].[DEBIT1]/1.065)*0.025,([group accounts
receivable].[debit1])*0.025)

I will need to subtract this value from debit1 (which is a payment) before
dividing by 1.065.

I really appreciate your quick response and thanks in advance.

Why update? Why store "sums"? Won't they change after every change to the
detail records?

Instead, consider just using a totals query to calculate the sums.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello, I need to update a field to the sum of another field in another table.
for example

I tried to something like UPDATE [PHYSICIAN GROUP] INNER JOIN [PHYSICIAN
RATE INFORMATION] ON [PHYSICIAN GROUP].[GROUP NUMBER] = [PHYSICIAN RATE
INFORMATION].[GROUP NUMBER] SET
[physician group].[gaf] = sum([physician rate information].[adfee] as
sumofadfee;

I basically want to sum based on group number in physician rate information
and assign this value to the group number in physician group.

any help would be greatly appreciated.

Thanks and have a nice day.

 

[Jeff Boyce]

My suggestion and question was about not using update queries.

I'm not understanding what your underlying business need is ... and it
sounds like the math you are proposing could be done in Excel. Have you
considered creating a query to get the raw data, then exporting to Excel to
do the calculations?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Yes, you are right because I will subtract a percentage of the adfee. How
do I create this totals query.

I will need this value to use in an exisiting update query such as
SET [GROUP TAX INFO].[1ST PAYMT SURPLUS] = IIf([does fet apply]=-1,([group
accounts receivable].[DEBIT1]/1.065)*0.025,([group accounts
receivable].[debit1])*0.025)

I will need to subtract this value from debit1 (which is a payment) before
dividing by 1.065.

I really appreciate your quick response and thanks in advance.

Why update? Why store "sums"? Won't they change after every change to the
detail records?

Instead, consider just using a totals query to calculate the sums.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello, I need to update a field to the sum of another field in another table.
for example

I tried to something like UPDATE [PHYSICIAN GROUP] INNER JOIN [PHYSICIAN
RATE INFORMATION] ON [PHYSICIAN GROUP].[GROUP NUMBER] = [PHYSICIAN RATE
INFORMATION].[GROUP NUMBER] SET
[physician group].[gaf] = sum([physician rate information].[adfee] as
sumofadfee;

I basically want to sum based on group number in physician rate information
and assign this value to the group number in physician group.

any help would be greatly appreciated.

Thanks and have a nice day.