Trying to write an expression in query designer

I have a table that has 3 columns.  One is Name.  Another is Code Name.  And the 3rd is Hours.

Using an test employee named "John Doe" I am trying to sum the hours in the table for him where the Code name started with "SN-2".

So if for example there were 3 records and one of them had a code name entered that was SN-2 Apple that had 1.5 hours, another that was SN-2 Peaches that had 4.3 hours, and a third record that had "SN-2 Pears" that had 5.2 hours I want to see 8.2 hours for the total.

I've tried this in the query designer but am not getting the correct result:

SN-2 Hours: Sum([Hours (Hours)] Like [Code Name]="SN-2" & "*")

Can someone help?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
In other words combined all of this into one expression:

SELECT [Employee Timesheet].Employee, Sum([Employee Timesheet].[Hours (Hours)]) AS [SN-2 Hours]
FROM [Employee Timesheet]
WHERE ((([Timesheet].[Code]) Like "SN-2" & "*"))
GROUP BY [Employee Timesheet].Employee;

Open in new window

als315Commented:
Can you upload sample DB with some dummy data? In question you say about one table, but in comment I see data from 2 tables (or "Timesheet.Code" is an error?)
Dale FyeOwner, Developing Solutions LLCCommented:
YOu might try:

SELECT [Employee Timesheet].Employee, Sum([Employee Timesheet].[Hours (Hours)]) AS [SN-2 Hours]
FROM [Employee Timesheet]
WHERE ([Timesheet].[Code] Like "*SN-2*")
GROUP BY [Employee Timesheet].Employee;

Open in new window

TONY TAYLORCommented:
All are good comments, but I prefer the below:

SELECT [Employee Timesheet].Employee, Sum([Employee Timesheet].[Hours (Hours)]) AS [SN-2 Hours]
FROM [Employee Timesheet]
WHERE ((Left([Timesheet].[Code],4) = "SN-2"))
GROUP BY [Employee Timesheet].Employee;

Open in new window

PatHartmanCommented:
I prefer changing the table since it is not normalized and that is what is causing the problem.  You seem to have two attributes mushed into a single column "SN-2" and whatever follows it.  I am assuming that there are also other combinations also.

A simple method that won't require you to actually fix the problem but will work as a band aid is to create another table named tblGroup.  This table will contain two columns.  The full text of all the values of Code and the second field would be the leading characters you are trying to group by.  Then to do the summing, you would join to tblGroup and select the group field and group by that.

SELECT tblGroup.GroupCD, [Employee Timesheet].Employee, Sum([Employee Timesheet].[Hours (Hours)])
FROM [Employee Timesheet] INNER JOIN tblGroup ON [Employee Timesheet].Code = tblGroup.GroupCD
GROUP BY tblGroup.GroupCD, [Employee Timesheet].Employee;

This will result in a list of "all" the groupings and the hours for each

SN-2 Sam 12.5
SN-3 Sam 20
AB-1 SAM 12

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.