Grouped Query to Table

[gmenon100 via AccessMonster.com]

Hello,

I have a access project which monitors invoices payment received. Each style
is a new record and 4-5 such records make up one invoice. i have grouped
these on a query to get a combined invoice total.

My question :

is it possible to move this grouped invoice to a new table (sumarized form).

the reason I need to do this is because i receive the payment in a lumpsum
with deductions and other charges which I cannot allocated to any single
record.

Is there any way, I can just put the amount paid against the combined records.


Any help is appreciated.

Gmenon

 

[Pieter Wijnen]

yes,
but it's better to write code (against your queries) to achieve you goal
the code however, would be someting like insert into sumtable(yourfields)
select yourfields from summarytable (+ criteria)

Pieter

Hello,

I have a access project which monitors invoices payment received. Each
style
is a new record and 4-5 such records make up one invoice. i have grouped
these on a query to get a combined invoice total.

My question :

is it possible to move this grouped invoice to a new table (sumarized
form).

the reason I need to do this is because i receive the payment in a lumpsum
with deductions and other charges which I cannot allocated to any single
record.

Is there any way, I can just put the amount paid against the combined
records.


Any help is appreciated.

Gmenon

--

 

[gmenon100 via AccessMonster.com]

Hi Pieter,

I am a novice and this seems too complicated, is it possible for you to
elaborate this.

Thank you for your response, highly appreciated.

yes,
but it's better to write code (against your queries) to achieve you goal
the code however, would be someting like insert into sumtable(yourfields)
select yourfields from summarytable (+ criteria)

Pieter

Hello,

[quoted text clipped - 18 lines]

Gmenon

 

[Pieter Wijnen]

try New Query, SQL Pane:

INSERT INTO InvoiceTotal (INVOICENO,TOTALAMOUNT)
SELECT A.INVOICENO, A.TOTALAMOUNT FROM YourGroupedQuery A
WHERE NOT EXISTS (SELECT 'X' FROM InvoiceTotal B
WHERE B.INVOICENO=A.INVOICENO)

Substituting Table/Field & Query Names For yours offcourse , Note that I "as
always" Alias the Tables in the Select Statement (A & B in this case)
You might want to substitute the WHERE clause with a Parameter instead ie

PARAMETERS Forms!YourForm!InvoiceNo Text;
INSERT INTO InvoiceTotal (INVOICENO,TOTALAMOUNT)
SELECT A.INVOICENO, A.TOTALAMOUNT FROM YourGroupedQuery A
WHERE A.INVOICENO = Forms!YourForm!InvoiceNo

If you (as you should) Add a unique index to InvoiceTotal (INVOICENO), You
can safely run it as often as you like <g>

HTH

Pieter

Hi Pieter,

I am a novice and this seems too complicated, is it possible for you to
elaborate this.

Thank you for your response, highly appreciated.

yes,
but it's better to write code (against your queries) to achieve you goal
the code however, would be someting like insert into sumtable(yourfields)
select yourfields from summarytable (+ criteria)

Pieter

Hello,

[quoted text clipped - 18 lines]

Gmenon