Solved

Access 2000 - Query to count records

Posted on 2013-06-25
10
703 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39276703
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
ID: 39276852
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
ID: 39287734
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:goldieretriever
ID: 39288288
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
ID: 39292071
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39292197
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
ID: 39302119
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
ID: 39302740
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
ID: 39302784
Thank you so very much! This is exactly what I'm looking for!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39303010
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

688 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