Add number fields, and concanate text fields in duplicate records

[bumfuzzled]

Every week I import timehsheets and they may have duplicate entries So I end up with something that looks like this

Empl InvoiceIDActivityDate LeaseName Hours
703 136067 5/22/2008 Link Ranch #1 1.5
703 136067 5/22/2008 Link Ranch Bndry 10
What I need to do is filter for duplicates, and then add the hours field together, and add the comments and Lease name field of the second record onto the end of the same fields in the first record first record. I might have as many as five or six duplicates.
Here is my SQL statment so far, I am clueless from here

 SELECT [Copy Of Time].EmployeeID, [Copy Of Time].InvoiceID, [Copy Of Time].ActivityDate, [Copy Of Time].LeaseName, [Copy Of Time].Hours, [Copy Of Time].Comments FROM [Copy Of Time]WHERE ((([Copy Of Time].EmployeeID) In (SELECT [EmployeeID] FROM [Copy Of Time] As Tmp GROUP BY [EmployeeID],[InvoiceID],[ActivityDate] HAVING Count(*)>1  And [InvoiceID] = [Copy Of Time].[InvoiceID] And [ActivityDate] = [Copy Of Time].[ActivityDate])))ORDER BY [Copy Of Time].EmployeeID, [Copy Of Time].InvoiceID, [Copy Of Time].ActivityDate;

 

[dhookom]

Check the FAQs in this forum for "Concatenate". Also, it would have helped if you had typed in your desired display of the data.

Duane
Hook'D on Access
MS Access MVP

 

[bumfuzzled]

What I am looking for would be

703 136067 Link Ranch #1,Link Ranch Boundry 11.5 hours

 

[lespaul]

then faq701-4233 would be the one for you!

Leslie

Have you met Hardy Heron?

 

[dhookom]

Did you check the FAQs specifically faq701-4233?

Duane
Hook'D on Access
MS Access MVP

 

[bumfuzzled]

yes, I did, and I am way over my head. I understand it, but not well enough to apply it here.

Thank you anyways

 

[dhookom]

I'm not sure where the comments field came from since it doesn't seem to be in your sample.

First copy the code into a new module in your MDB. Save the module with the name "modStringFunctions". Then create a totals query [qgrpEmpInvActDate] like:

SELECT EmployeeID, InvoiceID, ActivityDate, Sum([Hours]) as HrsSum
FROM [Copy Of Time]
GROUP BY [EmployeeID],[InvoiceID],[ActivityDate];

Then create a new query that performs the concatenation:

SELECT EmployeeID, InvoiceID, ActivityDate, HrsSum,
Concatenate("SELECT [Comments] & ' ' & [LeaseName]
   FROM [Copy Of Time] cot
   WHERE [Copy of Time].EmployeeID = cot.EmployeeID AND [Copy of Time].InvoiceID = cot.InvoiceID AND [Copy of Time].ActivityDate = cot.ActivityDate) as AllCommentsLeaseNames
FROM qgrpEmpInvActDate;

Duane
Hook'D on Access
MS Access MVP

 

[bumfuzzled]

thank you so much, you are right the comments field was left off. How do I add the hours field together?

 

[bumfuzzled]

duh i didn't read your post well enough, sorry

 

[bumfuzzled]

I read up on SQL this weekend, and I realized that I mis-stated what I want to do. I need to combine, permanently the LeaseName, Hours, and Comments field in two or more records when the Invoice# and Date and EmployeeID are the same. The duplicate records are throwing my totals in my reports off

Thanks in Advance,

Annie Wolf

 

[dhookom]

We can't see your SQL view, your results, and what you would expect.

Duane
Hook'D on Access
MS Access MVP

 

[bumfuzzled]

what i have so far is at the top of the thread, lets say i have two rows that look like this

InvoiceID EmployeeID Date LeaseName Hours Comments
135655 733 5-10 Link Ranch 2.5 Bndry
135655 733 5-10 Link Rnch #1h 1 well

What I need is

135655 733 05/10 Link Ranch, Link Rnch #1h, 3.5 Bndry, well

in one record

I may have more than one duplicate record sometimes as many as three or four.

I have been using access for two years, but I have never had to get into the SQL side of it very much until now

 

[lespaul]

Ok can you have more than one employee on an invoice ID like this:

[tt]
InvoiceID EmployeeID Date LeaseName Hours Comments
135655 733 5-10 Link Ranch 2.5 Bndry
135655 733 5-10 Link Rnch #1h 1 well
135655 734 5-10 Link Rnch #2 1.5 john's
[/tt]

will the date of the invoice ID always be the same?

Leslie

Have you met Hardy Heron?

 

[dhookom]

I believe I asked for the SQL view. I would expect something like:

SELECT EmployeeID, InvoiceID, ActivityDate, HrsSum,
Concatenate("SELECT [Comments] & ' ' & [LeaseName]
   FROM [Copy Of Time] cot
   WHERE [Copy of Time].EmployeeID = cot.EmployeeID AND [Copy of Time].InvoiceID = cot.InvoiceID AND [Copy of Time].ActivityDate = cot.ActivityDate) as AllCommentsLeaseNames
FROM qgrpEmpInvActDate;

Duane
Hook'D on Access
MS Access MVP

 

[bumfuzzled]

yes you can have more than one employee but they would not be combined, only the records with the same employee, same invoiceID, and Date. Basically, we round up charges from a half hour to whole, so if you have two entries from the same employee on the same date and invoice number, we wind up charging them 2 hours when it should be 1 hour. The SQL view for the find duplicate query looks like this. Time is the actual table I am working in Copy of Time was is a duplicate table that I am working this stuff out in.

SELECT Time.EmployeeID, Time.InvoiceID, Time.ActivityDate, Time.EmployeeInitials, Time.ChargeDescription, Time.Hours, Time.BillableHours, Time.LeaseName
FROM [Time]
WHERE (((Time.EmployeeID) In (SELECT [EmployeeID] FROM [Time] As Tmp GROUP BY [EmployeeID],[InvoiceID],[ActivityDate] HAVING Count(*)>1  And [InvoiceID] = [Time].[InvoiceID] And [ActivityDate] = [Time].[ActivityDate])))
ORDER BY Time.EmployeeID, Time.InvoiceID, Time.ActivityDate;

 

[dhookom]

I don't see any GROUP BY in the query. My solution was in a reply on [blue]13 Jun 08 10:02[/blue] and you seem to have ignored it. If you didn't understand the solution, you should aske for clarification.

Duane
Hook'D on Access
MS Access MVP

 

[bumfuzzled]

GROUP BY is in the above SQL View it says GROUP BY [EmployeeID],[InvoiceID], [ActivityDate]
My understanding of the SQL statement that you wrote is that it will combine the records for a view, but is not permanently updating the first recored with the information from the other "duplicate" recoreds, which is what I need to do.

 

[lespaul]

why do you need to update anything? any time you need to see the concatenated results run the query....

Leslie

Have you met Hardy Heron?

 

[bumfuzzled]

I need to "combine" these records, because the duplicate entries are throwing off my totals. Two .5 hour entries rounded to an hour, produces two hours, when in reality it should be two .5 hour entries combined producing 1 hour. In addition, someone does manual data entry of these records into another database and they are adding the records by hand before entering them, and that is causing problems with errors as well. I have around fifty timesheets that I import into my database, and it is becoming a big problem.

 

[dhookom]

Have you attempted to do as I suggested earlier? I would expect you would create two queries. What are the SQL views of these two queries? If you don't have two queries, go back and find my earlier suggestion.

Duane
Hook'D on Access
MS Access MVP