Help with and Update query

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hello,
I have an Access table with
tmpReportIndex PgNo AcNo NumPgs
1 0010 0
2 0010 0
3 0108 0
4 0108 0
5 0108 0
6 0138 0


and I want an update query to give me
tmpReportIndex PgNo AcNo NumPgs
1 0010 2
2 0010 2
3 0108 3
4 0108 3
5 0108 3
6 0138 1


where NumPgs will be the number of times AcNo occurs.
I tried
UPDATE tmpReportIndex
SET NumPgs=(SELECT Count(t.AcNo) AS CountOfAcNo FROM tmpReportIndex AS t
WHERE t.AcNo = tmpReportIndex.AcNo)
but no joy. I know this will work in SQLServer.

Thanks for any help.
Brian
 
In Access you will have to use the DCOUNT function. If AcNo is a text field
then use the following.

UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=""" & [AcNo] & """")

If AcNo is a number field change that to
UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=" & [AcNo])


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
John,
Years ago I had personnally found those D functions in Access to be slow
compared to the equivalent SQL and then I read other people recommending to
avoid them and I continued to avoid them.
No way to do this in pure SQL without the VBA function?
Thanks
Brian

John Spencer said:
In Access you will have to use the DCOUNT function. If AcNo is a text
field then use the following.

UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=""" & [AcNo] & """")

If AcNo is a number field change that to
UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=" & [AcNo])


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hello,
I have an Access table with
tmpReportIndex PgNo AcNo NumPgs
1 0010 0
2 0010 0
3 0108 0
4 0108 0
5 0108 0
6 0138 0


and I want an update query to give me
tmpReportIndex PgNo AcNo NumPgs
1 0010 2
2 0010 2
3 0108 3
4 0108 3
5 0108 3
6 0138 1


where NumPgs will be the number of times AcNo occurs.
I tried
UPDATE tmpReportIndex
SET NumPgs=(SELECT Count(t.AcNo) AS CountOfAcNo FROM tmpReportIndex AS t
WHERE t.AcNo = tmpReportIndex.AcNo)
but no joy. I know this will work in SQLServer.

Thanks for any help.
Brian
Click to expand...
Click to expand...
 
The only other way I know is build a temporary table with the results of the
aggregate query and then use the temporary table to update tmpReportIndex.

The D functions can be slow if a large number of records are being updated.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John,
Years ago I had personnally found those D functions in Access to be slow
compared to the equivalent SQL and then I read other people recommending to
avoid them and I continued to avoid them.
No way to do this in pure SQL without the VBA function?
Thanks
Brian

John Spencer said:
In Access you will have to use the DCOUNT function. If AcNo is a text
field then use the following.

UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=""" & [AcNo] & """")

If AcNo is a number field change that to
UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=" & [AcNo])


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hello,
I have an Access table with
tmpReportIndex PgNo AcNo NumPgs
1 0010 0
2 0010 0
3 0108 0
4 0108 0
5 0108 0
6 0138 0


and I want an update query to give me
tmpReportIndex PgNo AcNo NumPgs
1 0010 2
2 0010 2
3 0108 3
4 0108 3
5 0108 3
6 0138 1


where NumPgs will be the number of times AcNo occurs.
I tried
UPDATE tmpReportIndex
SET NumPgs=(SELECT Count(t.AcNo) AS CountOfAcNo FROM tmpReportIndex AS t
WHERE t.AcNo = tmpReportIndex.AcNo)
but no joy. I know this will work in SQLServer.

Thanks for any help.
Brian
Click to expand...
Click to expand...
Click to expand...
 
Hmm, fudge.
It's about 40,000+ records, I'll try and see which is better.
Thanks again.
Brian

John Spencer said:
The only other way I know is build a temporary table with the results of
the aggregate query and then use the temporary table to update
tmpReportIndex.

The D functions can be slow if a large number of records are being
updated.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John,
Years ago I had personnally found those D functions in Access to be slow
compared to the equivalent SQL and then I read other people recommending
to avoid them and I continued to avoid them.
No way to do this in pure SQL without the VBA function?
Thanks
Brian

John Spencer said:
In Access you will have to use the DCOUNT function. If AcNo is a text
field then use the following.

UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=""" & [AcNo] & """")

If AcNo is a number field change that to
UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=" & [AcNo])


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Brian wrote:
Hello,
I have an Access table with
tmpReportIndex PgNo AcNo NumPgs
1 0010 0
2 0010 0
3 0108 0
4 0108 0
5 0108 0
6 0138 0


and I want an update query to give me
tmpReportIndex PgNo AcNo NumPgs
1 0010 2
2 0010 2
3 0108 3
4 0108 3
5 0108 3
6 0138 1


where NumPgs will be the number of times AcNo occurs.
I tried
UPDATE tmpReportIndex
SET NumPgs=(SELECT Count(t.AcNo) AS CountOfAcNo FROM tmpReportIndex AS
t WHERE t.AcNo = tmpReportIndex.AcNo)
but no joy. I know this will work in SQLServer.

Thanks for any help.
Brian
Click to expand...
Click to expand...
Click to expand...
 
Of course, the question that comes to mind is why store this value at all.
You can compute it in a query whenever you need it and it will be more likely
to be accurate.



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hmm, fudge.
It's about 40,000+ records, I'll try and see which is better.
Thanks again.
Brian

John Spencer said:
The only other way I know is build a temporary table with the results of
the aggregate query and then use the temporary table to update
tmpReportIndex.

The D functions can be slow if a large number of records are being
updated.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John,
Years ago I had personnally found those D functions in Access to be slow
compared to the equivalent SQL and then I read other people recommending
to avoid them and I continued to avoid them.
No way to do this in pure SQL without the VBA function?
Thanks
Brian

In Access you will have to use the DCOUNT function. If AcNo is a text
field then use the following.

UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=""" & [AcNo] & """")

If AcNo is a number field change that to
UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=" & [AcNo])


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Brian wrote:
Hello,
I have an Access table with
tmpReportIndex PgNo AcNo NumPgs
1 0010 0
2 0010 0
3 0108 0
4 0108 0
5 0108 0
6 0138 0


and I want an update query to give me
tmpReportIndex PgNo AcNo NumPgs
1 0010 2
2 0010 2
3 0108 3
4 0108 3
5 0108 3
6 0138 1


where NumPgs will be the number of times AcNo occurs.
I tried
UPDATE tmpReportIndex
SET NumPgs=(SELECT Count(t.AcNo) AS CountOfAcNo FROM tmpReportIndex AS
t WHERE t.AcNo = tmpReportIndex.AcNo)
but no joy. I know this will work in SQLServer.

Thanks for any help.
Brian
Click to expand...
Click to expand...
Click to expand...
 
It is part of a process to sort and collate statements in a massive text
file and it works better when the info is in the temp table. Once the
exercise starts the data won't change.
Thanks
Brian

John Spencer said:
Of course, the question that comes to mind is why store this value at all.
You can compute it in a query whenever you need it and it will be more
likely to be accurate.



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hmm, fudge.
It's about 40,000+ records, I'll try and see which is better.
Thanks again.
Brian

John Spencer said:
The only other way I know is build a temporary table with the results of
the aggregate query and then use the temporary table to update
tmpReportIndex.

The D functions can be slow if a large number of records are being
updated.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Brian wrote:
John,
Years ago I had personnally found those D functions in Access to be
slow compared to the equivalent SQL and then I read other people
recommending to avoid them and I continued to avoid them.
No way to do this in pure SQL without the VBA function?
Thanks
Brian

In Access you will have to use the DCOUNT function. If AcNo is a text
field then use the following.

UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=""" & [AcNo] & """")

If AcNo is a number field change that to
UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=" & [AcNo])


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Brian wrote:
Hello,
I have an Access table with
tmpReportIndex PgNo AcNo NumPgs
1 0010 0
2 0010 0
3 0108 0
4 0108 0
5 0108 0
6 0138 0


and I want an update query to give me
tmpReportIndex PgNo AcNo NumPgs
1 0010 2
2 0010 2
3 0108 3
4 0108 3
5 0108 3
6 0138 1


where NumPgs will be the number of times AcNo occurs.
I tried
UPDATE tmpReportIndex
SET NumPgs=(SELECT Count(t.AcNo) AS CountOfAcNo FROM tmpReportIndex
AS t WHERE t.AcNo = tmpReportIndex.AcNo)
but no joy. I know this will work in SQLServer.

Thanks for any help.
Brian
Click to expand...
Click to expand...
Click to expand...
 
Ok, so the data is very static and storing the value will make the analysis
queries faster. That's reason enough for me.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
It is part of a process to sort and collate statements in a massive text
file and it works better when the info is in the temp table. Once the
exercise starts the data won't change.
Thanks
Brian

John Spencer said:
Of course, the question that comes to mind is why store this value at all.
You can compute it in a query whenever you need it and it will be more
likely to be accurate.



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hmm, fudge.
It's about 40,000+ records, I'll try and see which is better.
Thanks again.
Brian

The only other way I know is build a temporary table with the results of
the aggregate query and then use the temporary table to update
tmpReportIndex.

The D functions can be slow if a large number of records are being
updated.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Brian wrote:
John,
Years ago I had personnally found those D functions in Access to be
slow compared to the equivalent SQL and then I read other people
recommending to avoid them and I continued to avoid them.
No way to do this in pure SQL without the VBA function?
Thanks
Brian

In Access you will have to use the DCOUNT function. If AcNo is a text
field then use the following.

UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=""" & [AcNo] & """")

If AcNo is a number field change that to
UPDATE tmpReportIndex
SET NumPgs = DCount("*","tmpReportIndex","AcNo=" & [AcNo])


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Brian wrote:
Hello,
I have an Access table with
tmpReportIndex PgNo AcNo NumPgs
1 0010 0
2 0010 0
3 0108 0
4 0108 0
5 0108 0
6 0138 0


and I want an update query to give me
tmpReportIndex PgNo AcNo NumPgs
1 0010 2
2 0010 2
3 0108 3
4 0108 3
5 0108 3
6 0138 1


where NumPgs will be the number of times AcNo occurs.
I tried
UPDATE tmpReportIndex
SET NumPgs=(SELECT Count(t.AcNo) AS CountOfAcNo FROM tmpReportIndex
AS t WHERE t.AcNo = tmpReportIndex.AcNo)
but no joy. I know this will work in SQLServer.

Thanks for any help.
Brian
Click to expand...
Click to expand...
Click to expand...
 
Back
Top