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
Solved

Access 2000 - Query to count records

Posted on 2013-06-25
10
688 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adding new field to an existing database in access 3 26
90 days before current date 12 31
Run Access2013-32bit under WinXP? 4 31
Access Schema 6 25
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

860 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