reports! (i'm a newbie in access)

[dette]

i have the following table:

tblOnSiteCoaching : table *this is the main table

CoachingID *primary key
ImpAgency *implementing agency
TypeofTraining *type of training
ActualCost

tblAsstAgency : subtable *a subtable of the main table, allows multiple entry in datasheet form...

CoachingID *refers to the value in main table
AsstAgency *assisting Agency(ies)

in my query i have the following data:

CoachingID ImpAgency AsstAgency TypeofTraining ActualCost
1 DILG CCBRMO Technical 10,000.00
1 DILG RCBRMO Technical 10,000.00
1 DILG LGU Technical 10,000.00
2 DENR RCBRMO Institutional 15,000.00
2 DENR LGU Institutional 15,000.00
3 ERDB CCBRMO Technical 10,000.00
3 ERDB RCBRMO Technical 10,000.00

my problem is:

1) how can i make my report look like this:

ImpAgency AsstAgency TypeofTraining ActualCost
DILG CCBRMO Technical 10,000.00
RCBRMO
LGU
DENR RCBRMO Institutional 15,000.00
LGU
ERDB CCBRMO Technical 10,000.00
RCBRMO

2) how can i get the total/sum of ActualCost, counting the values with unique ID only. for example in #1, the total should be 35,000.00 but when i tried =Sum([ActualCost]) it addedd up all the values in the query, thus, giving me a value of 80,000.00


i need the solution to these problems really badly. i would appreciate any help you could give me! thank you in advance!

 

[Pat Hartman]

You need to use a report for the data from tblOnSiteCoaching with a subreport for the data from tblAsstAgency.

 

[dette]

thanks...i have another problem...

thank you so much pat!

i have another problem, this time it's about data entry in forms. i have a subtable, say

tblLGUandPO : subtable
SiteEstabID *gets the value from the main table
dtmDateVisited *date each LGU or PO visited
strLGUname *has a lookup
strPOname *has a lookup but depends on the entry in strLGUname

my problem is, everytime i choose a new item from strLGUname, the strPOname leaves a blank record and stores the selected item from strPOname to the next record (the form is in datasheet view) and it looks like this:

SiteEstabID| dtmDateVisited| strLGUname | strPOname |
1 | 04 oct 2004 | LGUname1 | POname1-1
1 | 04 oct 2004 | LGUname1 | POname1-2
2 | 05 oct 2004 | LGUname2 |
2 | 05 oct 2004 | LGUname2 | POname2-1
2 | 05 oct 2004 | LGUname2 | POname2-2
2 | 05 oct 2004 | LGUname2 | POname2-3
3 | 06 oct 2004 | LGUname3 |
3 | 06 oct 2004 | LGUname3 | POname3-1

how can i eliminate the blank record?

thanks again!

 

[Pat Hartman]

You have some code that is moving the record pointer. Post it.