Help With a Query That Has Repeated Rows

[Jamsandwich]

Hey guys,

Sorry about the title, but I was struggling a bit...

I'll try to explain my problem here -

I have 3 tables: People, Projects & Functions. They have the following layout -

People

Name (Key)	Contract Hrs
Steve	37
Bill	37
John	37
Sue	37
Andy	37

<tbody>
</tbody>

Projects

Number (Key)	Title	Lead
1	Apples	Peter
2	Bananas	Peter
3	Cherries	John
4	Dragonfruit	Erin
5	Elderberries	Erin

<tbody>
</tbody>

This table isn't so important for the question but adds background.

Functions

ID (Key)	Project #	Person	Hrs Required
1	1	Steve	1
2	1	Sue	3
3	1	Bill	2
4	2	Bill	2
5	3	Steve	2
6	4	Sue	1
7	4	John	2
8	5	Steve	2
9	5	John	2
10	5	Andy	5

<tbody>
</tbody>

The tables have a bit more than this, but we have enough here. So I am trying to put together a report which shows all of the details of each person and also has a text box connected to each person which shows the amount of hours they have available (Contract Hrs - Sum of Hrs Required).

To do this I have created queries. The first is simple: It gives me the sum of each person's Hrs required, grouped by person. This has the extra fields 'Person' (from function table) and 'SumofHrsRequired'. This query works fine.

The second query is where I am struggling. I created it and it appeared to work at first.

Field	Person	Contract Hours	SumofHrsRequired	HrsAvailable
Table	Query1	People	Query1
Total	GroupBy	GroupBy	GroupBy	GroupBy

<tbody>
</tbody>

HrsAvailable is - [Peple]![ContractHrs]-[Query1]![SumofHrsRequired]

Now, this appears to work when I create the Textbox on the report with a DLookup. The problem occurs when I change values in the 'Contract Hrs' field for individual people.

So if I change Steve to 35hrs and Sue to 30 hrs (for example), my query returns something like this -

Person	Contract Hrs	SumofHrs	HrsAvailable
Steve	30	5	25
Steve	35	5	30
Steve	37	5	32
Bill	30	4	26
Bill	35	4	31
Bill	37	4	33
John	30	4	26
John	35	4	31
John	37	4	33
Sue	30	4	26
Sue	35	4	31
Sue	37	4	33
Andy	30	5	25
Andy	35	5	30
Andy	37	5	32

<tbody>
</tbody>

This makes the value on my report come out wrong. I think it just goes with the first row for each person. Clearly, I've screwed up on my query but I don't know how to fix it.

Could somebody help me fix this?

 

[Jamsandwich]

Oh man, I've managed to sort this out. Panic over, I think.

I think the problem was that I was querying both the first query and the 'People' table.

So now I have added a Contract Hrs field to the first query. This means that for the 2nd query I only need to link it to the 1st (and I took the 'People' table out).

This appears to give me the values that I need.