Access 2000 - Query to count records

I need to create an export file which needs a line number for each of the employees records -  The data I'm working with now, I have two employees with 20+ lines of data so I need to start count over when it hits the next employee records.

I hope this makes sense, please let me know if you need more information to solve this question.
goldieretrieverAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
OK, try this.

Add a Primary Key to the table called: ID
Make it an AutoNumber Datatype
Then create a query like this from the table:

SELECT ExportBuildDetail.ID, ExportBuildDetail.EXT_EMP_ID, 1+DCount("EXT_EXP_ID","ExportBuildDetail","ID<" & [ID] & "And EXT_EMP_ID=" & "'" & [EXT_EMP_ID] & "'") AS EXP_RPT_LN
FROM ExportBuildDetail
ORDER BY ExportBuildDetail.EXT_EMP_ID;


When you see this work (and add in the line numbers), then you can add the other fields to the query.
Then you can export the *query* as a delimited file.

You can see this working in the query called: Query2

JeffCoachman
BuildImportExpense.mdb
0
 
SharathData EngineerCommented:
Do you have any date column to rank these records? If yes, you can try like this. change the table/column names as per your database.
SELECT employee_name, ( 
    SELECT COUNT(*) 
    FROM employee_table t2 
    WHERE t2.employee_name = t1.employee_name 
    AND t2.emp_create_date <= t1.emp_create_date 
) AS rank
FROM employee_table AS t1
ORDER BY emp_create_date

Open in new window

0
 
goldieretrieverAuthor Commented:
This worked for counting the number of rows each employee has in the table which I will need later, but now I need to increment each row for the employee by 1 and when reading the next employee I need to start over to count their rows incrementing by 1.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeffrey CoachmanMIS LiasonCommented:
1. Please clearly define what you are calling a "Line Number"?
2. Please post a clear graphical example of the *exact* output you are looking for...
3. First you say you just need a count of the rows, not you say you want to see them all "Incremented", so again, ...Please post a clear graphical example of the *exact* output you are looking for...
0
 
goldieretrieverAuthor Commented:
I'm importing a file from a Travel Expense Report Application into a Access Table then formating that data to create an export file that will be imported into our Accunts Payable Application.

When I'm writing out the data I have a header row and multiple detail rows - each of the detail Rows have a line number field which I misstated as a Count.

The first detail row for that employee is line number field is 1, the next is 2, the next is 3, etc.then when it hits the next employee detail row, the line number starts over with 1, then 2 until each  of employee detail rows have incremented by 1.

I haven't tried it yet, but I think I can use the solution from above to create a table with the employee number, expense report ID and the count - then using that table to populate the line number field using a loop linking the detail table to the count table and updating the line number field by incrementing by 1 until the count is matched.

I was hoping when running the query to create the detail table that reads the imported  expense data table, to populate the line number field at the same time without having to run multiple queries/functons.

I hope this is clearer than my original question. Any help or pointers will be greatly appreciated.
0
 
goldieretrieverAuthor Commented:
I haven't been able to create a function to loop through my detail records and update the line_number field.

I used the expert comment above and created a table with three fields: Exp_Report_ID, Exp_Emp_No, and rowCount

How can I use this new table to link to the detail table and loop through the detail table to update the line number field until the line number field equals the empCount then move to the next Exp_Report_ID and Exp_Emp_no until their line number detail rows match the empCount?

Or if I'm thinking wrong on this, please point me in the right direction to get this field updated.

Thank you
0
 
Jeffrey CoachmanMIS LiasonCommented:
Even more confused now.

I would need to see a sample database.
Please follow these steps:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.


JeffCoachman
0
 
goldieretrieverAuthor Commented:
I've attached a sample of my Access 2000 database, I have a table ExportBuildDetail-WHAT IT SHOULD LOOK LIKE is what I will be exporting as a comma delimited file.

Thank you in advanced for your help!
BuildImportExpense.mdb
0
 
goldieretrieverAuthor Commented:
Thank you so very much! This is exactly what I'm looking for!!!
0
 
Jeffrey CoachmanMIS LiasonCommented:
Great!

Once you explained it to me, it was clear.

Just note that there are probably other (more efficient) way of doing this.

But I am glad it worked for you...

;-)

Enjoy the weekend


JeffCoachman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.