Dsum Problem

[pinkpanther6666]

Hi All,

I've got a dsum problem and hoping somebody could help me please

2 Tables set up as below

tblmain

Week,Team,Player,DS,DD

tblFixtures

Week,Team,HDa,AWa

HDa is Home Darts
AWa is Away Darts

frmFixtures

What i need to do is for the Fixtures form textbox called HDa to have a total of [DS] + [DD/2] pulling the data from tblmain based on the values of Week and Team in frmfixtures


Many Thanks in Advance


Steve

 

[pinkpanther6666]

Hi All,

In the Control source for the Home team HDa ive Tried the following and doesnt work

=DSum([DS]+[DD]/2,"Main","[Week] = " & [Week] And "[HomeTeam] = " & [Team])

Can Anyone help please

Cheers


Steve

 

[Joe4]

Is there a reason you cannot link the two tables together in a query, and do the calculations there, and then just use the query as the Data Source of your form?
Whenever possible, I recommend doing most of the calculations in an underlying query instead of on the form itself.

 

[pinkpanther6666]

Thanks joe I will
Have a go

Many thanks

Steve

 

[pinkpanther6666]

Joe

Ive tried using

=DSum([Total],"qry123","[Week] = " & [Week] And "[HomeTeam] = " & [Team])

and still returns #Name

Hope you can help


Steve

 

[Joe4]

If you join your tables together properly, you should not need to use a DSUM formula at all.
If you post some sample data of what your tables look like along with your expected results, we can help you structure the query to do that.

 

[pinkpanther6666]

Hiya Joe

Table structures are



tblmain

Week,Team,Player,DS,DD

tblFixtures

Week,Team,HDa,AWa

HDa is Home Darts
AWa is Away Darts


Expected results are

Week,Home Team,Pts,Away Team,Pts

1,Team1,7,Team2,5
1,Team3,3,Team4,9
1,Team5,1,Team6,11


Hope this Helps and many thanks for your help


Steve

 

[pinkpanther6666]

Joe and All

Tried this but still doesnt work


=DLookUp([Total],[qry123],"Week =" & [qry123].[Week] And "Team =" & [qry123].[HomeTeam])


Regards

Steve

 

[Joe4]

The whole point of including all the needed tables in a query is so that you do NOT need to use DSUM and DLOOKUP functions. You use DSUM and DLOOKUP when the table/query you need is not included in the data source.

I was hoping for a little more detail/real examples of your data. Note that you can Copy and Paste from Table/Query results windows here, like this:

ID	Receipt Total	MyName	MyDate
0001	10	Joe	1/1/2016
0001	11	Jim	2/1/2016
0002	20	Bob
0004	30		4/1/2016
0005	40	Joe	6/1/2016

<caption> Table1 </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

If you could do this for the two underlying Tables that are involved, that would be very helpful. Then your expected output would make more sense (assuming that the expected output you post can be derived from your data examples).