Calculating values in Query

[Lensmeister]

Here I am again folks looking for help please.


I have a Access 2002 DB.

In it I have a table called tbl_StartingXI.

The fields are:
MatchID, Player, TimeOn, TimeOff, Yellow, Yellow_Time, Red, Subbed, Replaced.

I have a qry_CalcTimeOn and another called qry_AddUpTime

Using the queries, the qry_CalcTimeOn should add up the time that a player plays, and then the qry_AddUpTime should add up the qry_CalcTimeOn and report how many minutes a player played during a season/career.

Can anyone help me as these don't seem to be working I have probably done something dopey

I have attached a small sample data DB if anyone can help.

Thanks all,

Lensmeister.

 

[vbaInet]

Have a look at both queries to see how it was done.

One thing I noticed was that in your StartingXI table you had no Primary Key. I've fixed that by making PlayerID and MatchID Primary keys.

Secondly, the datatype for MatchID in StartingXI was Text but in Matches table it's Number (Autonumber). Linked fields should have the same datatype. Again, I fixed that by updating the field and getting rid of the text part and simply applied the Format you had in the Matches table to the MatchID field in the StartingXI table. MatchID needed to be fixed to be able to get the sum per Season.

 

[Lensmeister]

Thanks, but I cannot open it.

It gives a message:

This file is located outside your intranet or on an untrusted site. Microsoft Acces will not open the file due to potential security problems.

to open the file, copy it to your machine or an accessible network location.

I did save as from this site. but it will not open

Using Google Chrome BTW.

EDIT: Also same happenes when d/l using IE7.

Anyone ideas ?

 

[vbaInet]

You should be able to download and extract the zip folder attached.

 

[Lensmeister]

Thanks.

I will continue to work on it.



I see the problems now.

 

[vbaInet]

Good! Was that the result you were after?

 

[Brianwarnock]

Thanks, but I cannot open it.

It gives a message:


I did save as from this site. but it will not open

Using Google Chrome BTW.

EDIT: Also same happenes when d/l using IE7.

Anyone ideas ?

Having copied it to your machine go into explorer, right click on the file select properties from the local menu that opens, then unblock from the next menu.

Brian

 

[Lensmeister]

Cheers Brian.

vbsInet,

Yes. I want to be able to calculate the number of minutes a player played for a club in his career. But your version divided it up into season which was even better. So I copied that query and removed the season part of the query, and it still worked.

I may very well have some other items to calculate and and some other question on this one.

Thanks again ... you're a star.

 

[vbaInet]

Glad we could help.