Solved

Access 2000 - Query to count records

Posted on 2013-06-25
10
650 Views
Last Modified: 2013-07-05
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.
0
Comment
Question by:goldieretriever
  • 5
  • 4
10 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 

Author Comment

by:goldieretriever
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:goldieretriever
Comment Utility
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
 

Author Comment

by:goldieretriever
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:goldieretriever
Comment Utility
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:goldieretriever
Comment Utility
Thank you so very much! This is exactly what I'm looking for!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 41
ms sql stored procedure 22 75
Resize text 4 14
Need Distinct Value from Joined Tables in MSSQL 5 23
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now