Access can't count? Grouping records by 100's

[Guest]

Hello, we ran an employee incentive to get some data entry done, and we are
now trying to automate our method of picking winners.

All I need to do is to create a report that accurately lists the entered
records, 100 at a time, broken down by the persons name who entered it, and
sorted chronologically by the entry date.

The problem, however, is that even though I grouped the records by 100's in
a report, the actual count is almost never 100; it's usually less, but
occasionally more.

The report I created is grouped first by the record id, "ID", and I set the
grouping options to group by 100's. The next grouping field is "EnteredBy",
and the detail records show the record ID again, the TripNo field, and the
date entered. The top group, ID, has a footer field showing it's bad count,
and each EnteredBy record has a footer field showing that person's count, and
this count seems to be ok.

It's further strange because there is currently 388 records yet in the db,
but the report goes on to suggest that there is in excess of 400 records.

What gives? Any ideas on how I can fix this report or create another that
can get those counts right?

Any help greatly appreciated!!!

--jon

 

[Sophie Guo [MSFT]]

Hello Jon,

Based on the information you provided, I am unable to reproduce the issue
on my side. To troubleshoot the issue, I'd like to collect more information
for research:

1. Sample table and records

If the report is using a table as a record source, please post a sample
table here. It should include column name, column data type, etc.

If the report is using a query as record source, please post a sample table
and the select statement here.

Please post some sample records here.


2. Sample steps to reproduce the issue.

Please provide detailed steps to reproduce the scenario.

After I received the sample records and steps, I will test the issue on a
healthy computer and let you know the results as soon as possible.

I look forward to hearing from you.


Sophie Guo

Microsoft Online Partner Support


When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.


This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469

Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/


If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=/international.aspx.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi Sophie,

Thanks very much for your response! I have a sample recordset and report put
together, but see no way to post it here, so I'm putting it on my website for
you to download, at:

http://www.coastaldataenterprises.com/ReportBy100s.zip

It is a simple Access database with one table, and one report; the report is
broken down into groups so that it displays each sub count. I will also
double check that the results are the same on my computer at home tonight.

Thanks in advance, and let me know if you have any problem getting that
file, so that I can take it down afterwards.

--Jon

 

[Sophie Guo [MSFT]]

Hello Jon,

Thanks for your reply.

I have downloaded the .mdb file and tested it on a healthy computer.

I have noticed that the actual count of records is 406. The count of
"ModifiedBy" group and the count of "ID" group are correct. The problem is
that the records cannot be grouped correctly.

The cause of the issue is as followings:

Although you have set the type of the ID column as "AutoNumber", it is not
sequential. If you open the tblTrips_lkp table, you will find that after ID
22 it is ID 34.

For example, the number of records with ID between 1 and 100 is 61 which is
correctly grouped in the report.

To resolve the issue, I recommend you perform the following steps:

1. Open the tblTrips_lkp table in Design view.
2. Add a new column called "NewID", Data Type is "AutoNumber". "New
Values" properties is "Increment". Set it as primary key column.
3. Delete the "ID" column.
4. Recreate the "ID" column. Data Type is "Number".

Note: if you don't delete the "ID" column, the table cannot be saved.

5. Open the rptLineItemsBy100s report in Design view.
6. Group the report by the "NewID" column instead of the original "ID"
column.

If you do not want to remove ID column, you may want to create a new table
with new sequence column, export the data from the old table to make the
proper report you want.

I have tested above steps and it works.

Please let me know the results. I look forward to hearing from you.

Sophie Guo

Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Whoah, yikes, no, I can't re-create the primary key, but I could probably
re-produce that effect in a query, shouldn't I? ID's will always have some
fragmentation anyways, as records get deleted, but using a query I should be
able to get reliable results all the time.

Any ideas?

--Jon

 

[Sophie Guo [MSFT]]

Hello Jon,

Thanks for your reply.

Unfortunately, based on my knowledge, this function cannot be implemented
by a single query. You may need to write code in ADO.

You may use "select into" SQL statement to generate a table dynamically,
the ADO code may looks like the followings:

Function selectinto1(tName As String)

Dim sqlstr As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection

sqlstr = "select customerid as cuid into " & tName & "from
Customers;"

'conn.Execute "Create Table " & tName & "(" & colName & "
Identity(" & vSeed & ", " & vInc & "));"
conn.Execute sqlstr

sqlstr = "ALTER TABLE" & tName & " Add COLUMN myid
identity(1,1)"
conn.Execute sqlstr

Application.RefreshDatabaseWindow

End Function


Note: This programming example is provided for illustration only, without
warranty either expressed or implied, including, but not limited to, the
implied warranties of merchantability and/or fitness for a particular
purpose. This assumes that you are familiar with the programming language
being demonstrated and the tools used to create and debug procedures.
Microsoft support professionals can help explain the functionality of a
particular procedure, but they will not modify these examples to provide
added functionality or construct procedures to meet your specific needs. If
you have limited programming experience, you may want to contact a
Microsoft Certified Partner or the Microsoft fee-based consulting line at
(800) 936-5200. For more information about Microsoft Certified Partners,
please visit the following Microsoft Web site:
http://www.microsoft.com/partner/referral/


I hope above information is helpful.


Sophie Guo

Microsoft Online Partner Support



When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.



This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469

Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/



If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=/international.aspx.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.