Double entry on forms?

[sugar05]

Hello All again,

I am working on a form for my database this time and I have noticed something odd that I cant figure out. I have managed to make the following form (http://members.iinet.net.au/~a.beardsley/database/1.jpg) from a Staff Table, Activities Table and a Total Expense Query, all in the one form. My problem is that I only have two entrys in the activities (http://members.iinet.net.au/~a.beardsley/database/3.jpg) but I am recieving 4 entrys in the form. 2 of witch the totals do not add. (see other 2 entrys here: http://members.iinet.net.au/~a.beardsley/database/2.jpg). Does anyone know why this would be happening?

 

[boblarson]

My guess would be that in your totals query you have not linked ALL of the fields between the tables that have the same info. That would give you this kind of result. However, if that is not the case, then if you could post the database here, we might be able to spot the problem quickly and easily.

There may be others who can give you more info, but I'm visual, so seeing helps me.

 

[sugar05]

Ok that would be great. I have put a copy of the database I have so far online at this link for ppl to download:

http://members.iinet.net.au/~a.beardsley/database/StaffProLearning.mdb

I noticed something pretty interesting as well. When there is only one set of data in the activities table, only one set of data displays in the form (as it should). When 2 sets of data are in there, I get four entries on the field.

I also noticed that when you run the form called StaffTotalExpenses, you get 2 entrys on the form when there are 2 entrys in the Activites table. As it should. Odd....

 

[Pat Hartman]

I noticed something pretty interesting as well. When there is only one set of data in the activities table, only one set of data displays in the form (as it should). When 2 sets of data are in there, I get four entries on the field.

Sounds like your main form includes a join to the records being displayed on the subform. The mainform query should contain ONLY the mainform table plus any lookup tables. It should NEVER join to the many-side table. The subform query should contain only the subform table plus any necessary lookup tables. It should NEVER join to the one-side table.

2. As far as the subtotal goes, it looks like it is an unbound field. An unbound field will show the value for the current record on ALL visible records. So if you click in rec 1, you'll see $330. If you click in rec 2, you'll see $270. To solve this problem, move the calculation to the query so you can use a bound field. That way, each record will show the appropriate total.

 

[sugar05]

Thanks for your reply, but im not sure i completely understand what your saying. I have removed the link between Staff and Activities tables in the Query that I have created to try and recieve the total expense, but when I create a form i'm still getting 4 entrys. Further guidence would be helpful. I am not an advanced user of Access. Sorry...

 

[sugar05]

Dont worry, its all fixed now. I do have another question though in the Tables forum.