Summing totals if record satisfy criteria

[Guest]

Hi,

I have a query that displays certain information based on a form. I now want
to sum totals for certain information from the query. There is a few slot for
projects. There is also about 10 projects to choose from. For each project
slot, there is a corresponding Hour slot. I can for instance select the Blue
project in slot 1 and in slot 2 and the red project in slot 3 (this would be
one record). At the next record I can choose Red project at slot 1, Blue
project at slot 2 and Green project at slot 3. At the end I want to add up
all the amount of hours for each project (never mind the slot that I have
chosen). Like, display blue project, 5 hours, green project 6 hours, Red
project 2 hours. What I basically need to do, is to count the amount of hours
in a slot, based on certain criteria.

How can I do this?

 

[Duane Hookom]

It sounds like you need to normalize your table structure. However, we don't
know what your structure is or how to help you unless you provide us with
some sample data (with field names) and the desired sums.

 

[Guest]

Here is what I have at the moment:

I have a form with 8 slots for the project name and 8 slots for the Hours
corresponding to each project slot. From these project slots, the user can
select any of the projects (from a drop-down list which uses another table
listing all of the possible projects). My form writes into the final table
for which I have at the moment, a field for every project slot and a field
for all the corresponding project hours.

What do have to need to change? Should I only have one project field and one
hours field named "Projects" and "Hours" in my final table respectively and
somehow connect it to another table? And how would I get the hours to
correspond with a certain project chosen?

Thus, the user will have the option to choose a certain project for each
slot as well as the amount of hours on that project (on the form). At the
end, I would like to generate a report and sees how many hours was spent on
which project, irrelevant on which slots the selected which projects
(Sometimes a cetain project will be selected on the first slot, sometimes on
the second slot or sometimes on slot 1 and 3). What is important, is to know
how many hours was spent on a certain project. The hours must correspond with
the spesific project and I would like to sum these hours for each project at
the end or something similar.

Thanks!

 

[Duane Hookom]

"However, we don't know what your structure is or how to help you unless you
provide us with some sample data (with field names) and the desired sums."

 

[Guest]

I tried to describe my structure the best I could, but if you meant I must
sent you my database (or a sample of it in a file), please let me know where
I can do this...

Thanks!

 

[Duane Hookom]

Could you type the table and field names like:

Orders
============
OrderID autonumber primary key
OrderDate Date
CustomerID link to Customers.CustomerID
RequiredDate Date
.....

A few sample records typed into a reply would also be helpful.

 

[Guest]

Okay....let me explain what I have (even though it'ss is not correct)

I have a form where a user (which is the CurrentUser), can select a date
(from a calender) and there is a few slots to choose pre-entered projects and
also slots to enter corresponding hours spent on each project.

This form, writes into a table that looks like this:

ID,Name,Date,Project1, Project2,....., ProjectHours1, ProjectHours2 ,....


I suppose I must change it into the following (or something like this?)

Names and Dates (Primary table still with ID as its primary key)

ID Names Dates
23 John 22/05/1982
24 Mary 26/08/2003

The new table (Secondary table) should also have three fields: ID, Projects
and
ProjectHours.

ID Projects Hours
23 ProjectA 5
23 ProjectB 6
23 ProjectA 2
24 ProjectC 1
24 ProjectB 2

After this, I would probably be able to sum the totals for each spesific
project for every ID.

I haven't work with relationships yet, so don't know how to do this (if this
is the correct way to go). Which field do I have to connect with which fields?

My structure and everything work fine, but this change would be beneficial
to my database. So basically, I don't want to change anything else (If that
is possible).

Thanks!

 

[Duane Hookom]

You are getting closer but apparently you didn't realize that it would help
to know the "into a table" name. You can normalize your data with a union
query

SELECT ID, Name as Employee, Project1 as Project, ProjectHours1 as
ProjectHours
FROM tblNoName
WHERE Nz(ProjectHours1,0) >0
UNION ALL
SELECT ID, Name, Project2, ProjectHours2
FROM tblNoName
WHERE Nz(ProjectHours2,0) >0
UNION ALL
SELECT ID, Name, Project3, ProjectHours3
FROM tblNoName
WHERE Nz(ProjectHours3,0) >0
--etc--;

This would give you the ability to summarize hours by project. I would still
consider normalizing the table structure.