combining multiple like fields and totaling

[tryingtolearn]

Not exactly sure if a query is what I need in this situation or if it is what I need how to get there.

In the attached db example on the case form
there is a section for technicians to go in and take credit for steps that they performed as part of the overall case

So clv1 might be done by User A
Then clv2 might be done by User B

But the next case it might be switched.

I need a method getting the sum of the total clv's field for each technician in two different ways
1 would be the total clvs for USer A for the current month,
2nd would be the total for the year- or actually a prompt for a date range

Tried using the query wizard but it doesnt combine the names
Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.

Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?

Well the db example is imb so it cannot be attached

Thanks for any help.

 

[AlphaZeroX]

A query does indeed sound like what you need. You could try using the totals in the query design view wich is the omega button, then using the fields that have the materials you need added,multiplied, or subtracted from each other do such by the drop down list that appears fromt he new row after clicking the omega button. ( if your wondering what the omega button looks like, its the weird "E") This will add the field info you need.
Involving the prompt for a date range, a parameter could be used involving the criteria field.

 

[tryingtolearn]

Thanks for the fast reply,
I will try that out.

 

[tryingtolearn]

Well it seems to work sort of,
The problem is I get 2 columns

User A in the clvtech1 column
and
UserA in the clvtech2 column.

Is there something that can be done to go through
both fields (clvtech1 and clvtech2)
get all the instances of userA from both fields then return the sum of the clvs associated w/ userA from both clvtotal1 and clvtotal2?

 

[KeithG]

Look at the queries I have made. Is this what you are trying to get at?

 

[tryingtolearn]

Thanks KeithG

I think it may be - just not the fields I need,
I am looking into the qryALLCLVCodes query you wrote,
Its all new to me but I think I am following what you did.

Im going to tweak that a bit and see if I can get the technician fields in there so I know who did what.

Will be back in a bit and let you know.

Thanks for taking the time

 

[tryingtolearn]

Well maybe I dont understand
Everytime I try to change any of the values it saves but then I get a warning saying
Characters found after end of SQL statement.

Basically the fields I need to work with are
clvtech1 clvtech2
clvtotal1 clvtotal2

So it groups clvtech1 and clvtech2 together and then gives the sum from clvtotal1 and clvtotal2 for that technician

Lets say on record 1
TechA is in clvtech1 with 100 as the value in clvtotal1
TechB is in clvtech2 with 40 as the value in clvtotal2
On record two
TechB is in clvtech1 with 40 as the value in clvtotal1
TechA is in clvtech2 with 20 as the value in clvtotal2
On record tthree
TechB is in clvtech1 with 20 as the value in clvtotal1
TechC is in clvtech2 with 80 as the value in clvtotal2

I would need the query to list
Technician CLVs
TechA 120
TechB 100
TechC 80

Wow - Im even confusing myself here!
Does that make sense to you?

 

[tryingtolearn]

ALMOST There

I have the following two queries

Query 1

SELECT case.clvtotal1 as CLVCode, case.clvtech1
FROM [case];
UNION SELECT case.clvtotal2 as CLVCode, case.clvtech2
FROM [case];

Query 2
(The one I use to see the results)

SELECT Query1.clvtech1 AS Technician, Sum(Query1.CLVCode) AS CLVs
FROM Query1
GROUP BY Query1.clvtech1;

The last problem is

If techA has a clv value of 8 on record 1 and a value of 8 on record 4 it skips the second value and only counts it once.

Is there a way around that?

Thanks for the push in the right direction. Just about there

 

[pbaldy]

Not a direct answer to your question, but what are you going to do when 3 people work on a project? I would submit that your design is not normalized, because of the repeating fields (clvtech1, clvtech2...). Your UNION query is basically an attempt to normalize the data to make it easier to work with. I would consider normalizing the design before moving on.

 

[tryingtolearn]

hahaha Great,
Thats what they get for getting me to make the database!
Its for a dental lab - I make teeth for a living not databases

You are right - in the end there will be 20 of these blocks because each project can have up to 20 different codes all done by a handfull of technicians

But I have no idea how to do that or what it means - so Im plugging along trying to figure stuff out as I hit a wall - This has been the biggest wall so far.

 

[tryingtolearn]

I got the omitting problem fixed with
UNION ALL SELECT

What kind of problems am I in for down the road pbaldy??
Is what you were talking about a bad thing or will I be able to go on?

 

[pbaldy]

I make teeth for a living not databases

Hey, if you're making mine, speed it up! I've got a temporary in, waiting for a crown.

Your design is spreadsheet thinking instead of relational database thinking. Can you get away with it? Probably, but you may also keep running into problems you wouldn't have with proper design. Like I said, what happens when you have 3 techs? You have to add fields to your table & form, you'll have to change your UNION query to add the extra fields, etc. Proper design is dynamic, so an increase or decrease in the number of techs is handled seamlessly.

I've only glanced at your db, but your situation sounds like it could be compared to an invoice db. What you've got is one table with fields for each product sold on the invoice (product1, price1, product2, price2...). Proper design is one table for the invoice header info (date, customer...) and a separate table for the items sold on that invoice. Each item sold would be a record (row) in this table. If you sold 10 items, this table would have 10 rows in it. You can see how dynamic it will be, since then you can have 1 item or 1000 with no design changes necessary.

In your situation, your case table would continue to contain the information that is unique to that case, but the information related to each tech would be moved off to a new table, with the caseid field joining them together. Typically this relationship is displayed with a form/subform, with the form containing the data from the main table, and the subform containing the data from the detail table.

 

[tryingtolearn]

Wow,
Thanks for the info.

I think Im somewhere in the middle - I suppose I will start off with this and improve it as I learn more about it.
It is a lot more involved than I thought it would be.

Thank you for taking the time to explain that to me.

Stay away from the sticky foods w/ that temp - I'll put a rush on the case for ya!

 

[pbaldy]

No problemo, and good luck. I will say that it's a path we've probably all been down. When you start out, you just start putting forms and tables together without a lot of forethought (let's face it, when you're just starting you don't know what to think out anyway!). When you're experienced, you put a lot more work into a new application before you ever even open the Access program, because proper table layout is the foundation the whole thing is built on. If it's wrong to start with, it gets really hard to change it down the road. I guess I'm saying that a little extra work now can really pay off later (though since it's not your primary responsibility, I can see how that extra work isn't so attractive).