Group Update query

[Diarmuid]

hi
I have two table, InvoiceInfo and InvoiceLines. Invoice.thOurRef links
to InvoiceLines.OurRef. thOurRef is the primary key of InvoiceInfo, and
LineNo is an index in InvoiceLines.
I want to set InvoiceInfo to the number of lines in InvoiceLines.So I
set up a query to count the number of lines, called qryCountLines

SELECT InvoiceLines.OurRef, Count(InvoiceLines.LineNo) AS CountOfLineNo
FROM InvoiceLines GROUP BY InvoiceLines.OurRef;

Now I want to use this in my next query
UPDATE InvoiceInfo INNER JOIN qryCountLines ON InvoiceInfo.thOurRef =
qryCountLines.OurRef SET InvoiceInfo.NumTrans = [CountOfLineNo];

But then I get message "Operation must use an updateable query". How should
I go about this?

Thanks
Diarmuid

 

[Diarmuid]

Just to be clear, I want to update the field InvoiceInfo.NumTrans to the
number of lines. I want to generate this before I run a particular report. I
don't understand what you mean here - could you give a SQL statement?
Thanks
Diarmuid

Hi,

Join your master and detail in a update query, then update InvoiceInfo to
Nz(InvoiceInfo,0)+1. You don't need the Nz() if you know its not-null.
For
every line this adds one to the total you're updating - the more lines the
more updates.
Note maintaining the number of lines is not automatic with this method -
it's really better to generate the total when you need it.

Best REgards,

Peter

hi
I have two table, InvoiceInfo and InvoiceLines. Invoice.thOurRef
links
to InvoiceLines.OurRef. thOurRef is the primary key of InvoiceInfo, and
LineNo is an index in InvoiceLines.
I want to set InvoiceInfo to the number of lines in InvoiceLines.So I
set up a query to count the number of lines, called qryCountLines

SELECT InvoiceLines.OurRef, Count(InvoiceLines.LineNo) AS CountOfLineNo
FROM InvoiceLines GROUP BY InvoiceLines.OurRef;

Now I want to use this in my next query
UPDATE InvoiceInfo INNER JOIN qryCountLines ON InvoiceInfo.thOurRef =
qryCountLines.OurRef SET InvoiceInfo.NumTrans = [CountOfLineNo];

But then I get message "Operation must use an updateable query". How
should
I go about this?

Thanks
Diarmuid

 

[Peter Martin]

I meant to say 'NumTrans' instead of 'InvoiceInfo' in the original response-
my mistake.

If you want it on a report it would be easier (and more reliable) to use a
calculated field, not update a value in a table first. But you may have
other reasons for the update, so here goes. if *=Primary Key then I
understand you have tables

invoiceinfo
-----------------
thOurRef *
...
NumTrans


invoicelines
----------------
thOurRef*
LineNo *


the update would be
UPDATE InvoiceInfo INNER JOIN InvoiceLines ON InvoiceInfo.thOurRef =
InvoiceLines.thOurRef SET InvoiceInfo.NumTrans = [NumTrans]+1;

if NumTrans does not default to 0 then put the above as = Nz([NumTrans],0)+1;

You should zero NumTrans first before you add/remove lines.
UPDATE InvoiceInfo SET InvoiceInfo.NumTrans = 0;

You can restrict this just to an invoice your updating. Still, again, best
avoid this kind of denormalization if possible. Just outer join invoiceinfo
with

SELECT InvoiceLines.thOurRef, Count(InvoiceLines.LineNo) AS Lines
FROM InvoiceLines
GROUP BY InvoiceLines.thOurRef;

as the recordsource for your report. Or usually slower is using a database
function
SELECT InvoiceInfo.thOurRef, nz(DCount("LineNo","InvoiceLines","thOurRef=" &
[thOurRef]),0) AS NumTrans
FROM InvoiceInfo;

or you could just do it in the report code, count the details that are
generated.

It's always bothered me that some update queries that should be allowed
aren't. It is bothering me right now!

Best Regards,

Peter.

Just to be clear, I want to update the field InvoiceInfo.NumTrans to the
number of lines. I want to generate this before I run a particular report. I
don't understand what you mean here - could you give a SQL statement?
Thanks
Diarmuid

Hi,

Join your master and detail in a update query, then update InvoiceInfo to
Nz(InvoiceInfo,0)+1. You don't need the Nz() if you know its not-null.
For
every line this adds one to the total you're updating - the more lines the
more updates.
Note maintaining the number of lines is not automatic with this method -
it's really better to generate the total when you need it.

Best REgards,

Peter

hi
I have two table, InvoiceInfo and InvoiceLines. Invoice.thOurRef
links
to InvoiceLines.OurRef. thOurRef is the primary key of InvoiceInfo, and
LineNo is an index in InvoiceLines.
I want to set InvoiceInfo to the number of lines in InvoiceLines.So I
set up a query to count the number of lines, called qryCountLines

SELECT InvoiceLines.OurRef, Count(InvoiceLines.LineNo) AS CountOfLineNo
FROM InvoiceLines GROUP BY InvoiceLines.OurRef;

Now I want to use this in my next query
UPDATE InvoiceInfo INNER JOIN qryCountLines ON InvoiceInfo.thOurRef =
qryCountLines.OurRef SET InvoiceInfo.NumTrans = [CountOfLineNo];

But then I get message "Operation must use an updateable query". How
should
I go about this?

Thanks
Diarmuid

 

[Diarmuid]

Thanks, thats a great help. Just to let you know. I'm printing an invoice,
so I need the same number of lines on each page to keep the layout the same.
So I add blank lines to the bottom of the invoice, based on the number of
lines in the invoice.
Diarmuid

I meant to say 'NumTrans' instead of 'InvoiceInfo' in the original
response-
my mistake.

If you want it on a report it would be easier (and more reliable) to use a
calculated field, not update a value in a table first. But you may have
other reasons for the update, so here goes. if *=Primary Key then I
understand you have tables

invoiceinfo
-----------------
thOurRef *
..
NumTrans


invoicelines
----------------
thOurRef*
LineNo *


the update would be
UPDATE InvoiceInfo INNER JOIN InvoiceLines ON InvoiceInfo.thOurRef =
InvoiceLines.thOurRef SET InvoiceInfo.NumTrans = [NumTrans]+1;

if NumTrans does not default to 0 then put the above as =
Nz([NumTrans],0)+1;

You should zero NumTrans first before you add/remove lines.
UPDATE InvoiceInfo SET InvoiceInfo.NumTrans = 0;

You can restrict this just to an invoice your updating. Still, again,
best
avoid this kind of denormalization if possible. Just outer join
invoiceinfo
with

SELECT InvoiceLines.thOurRef, Count(InvoiceLines.LineNo) AS Lines
FROM InvoiceLines
GROUP BY InvoiceLines.thOurRef;

as the recordsource for your report. Or usually slower is using a
database
function
SELECT InvoiceInfo.thOurRef, nz(DCount("LineNo","InvoiceLines","thOurRef="
&
[thOurRef]),0) AS NumTrans
FROM InvoiceInfo;

or you could just do it in the report code, count the details that are
generated.

It's always bothered me that some update queries that should be allowed
aren't. It is bothering me right now!

Best Regards,

Peter.

Just to be clear, I want to update the field InvoiceInfo.NumTrans to the
number of lines. I want to generate this before I run a particular
report. I
don't understand what you mean here - could you give a SQL statement?
Thanks
Diarmuid

Hi,

Join your master and detail in a update query, then update InvoiceInfo
to
Nz(InvoiceInfo,0)+1. You don't need the Nz() if you know its not-null.
For
every line this adds one to the total you're updating - the more lines
the
more updates.
Note maintaining the number of lines is not automatic with this
method -
it's really better to generate the total when you need it.

Best REgards,

Peter


:

hi
I have two table, InvoiceInfo and InvoiceLines. Invoice.thOurRef
links
to InvoiceLines.OurRef. thOurRef is the primary key of InvoiceInfo,
and
LineNo is an index in InvoiceLines.
I want to set InvoiceInfo to the number of lines in
InvoiceLines.So I
set up a query to count the number of lines, called qryCountLines

SELECT InvoiceLines.OurRef, Count(InvoiceLines.LineNo) AS
CountOfLineNo
FROM InvoiceLines GROUP BY InvoiceLines.OurRef;

Now I want to use this in my next query
UPDATE InvoiceInfo INNER JOIN qryCountLines ON InvoiceInfo.thOurRef =
qryCountLines.OurRef SET InvoiceInfo.NumTrans = [CountOfLineNo];

But then I get message "Operation must use an updateable query". How
should
I go about this?

Thanks
Diarmuid