Solved

Access 2000 - Query to count records

Posted on 2013-06-25
10
665 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
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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

910 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

21 Experts available now in Live!

Get 1:1 Help Now