Working Out An Average

[Dannyboy11]

Hello

I'm very new to creating databases and working with Access, so please forgive me if this seems basic!

I'm going to put it in context for you... I am creating a database for my role as a Referee Secretary (Football in England) and I have a table that has referee names and marks. So the referee name will appear infinite amount of times and so will the number (0-100).

As an example, I have put it

Referee 1 | 100
Referee 1 | 60
Referee 2 | 60
Referee 2 | 74

I want to run a query so that I can enter in the name of the referee and it will return their average mark (which in this case would be 80 for 'Referee 1').

So the parameter is the referee name.

How can I do this please? I'd be very grateful.

If I can master this then later on I'd also like to find out how to create a report so it shows the average average and then based on this referees who are performing above average by x marks and those peforming below average by x marks and the highest scoring referee etc.

 

[Kryst51]

Create a query that averages the marks. Click the little greek E symbol group by refereeID, then use average on the marks. base a form on this query. Put a combo on the form that looks up the record selected in the combo.

I have attached an example

 

[Dannyboy11]

Hi

I've got to the form stage of selecting the drop down, but how do I make the referee name and the average display accordingly? I've looked at your example and when I try to copy it I just get #NAME?

 

[Kryst51]

Is your form bound to the query? Post what you have so I can see.

 

[Dannyboy11]

Hi

I feel really silly now but how do I post what I have?

Do you mean attach the database?

 

[Kryst51]

Hi

I feel really silly now but how do I post what I have?

Do you mean attach the database?

Yep, put in dummy info instead of real info, companct and repair, zip it up, and attach it.

If you are not already in it, you will need to be in advanced mode, then in the options above find the paperclip, this will bring up the attachment dialog box.

 

[Dannyboy11]

Ok I've attached it.

I didn't know what you meant by companct and repair so I just attached it as I felt I should!!

 

[Kryst51]

I am still looking at it but one general thing I see is that you have a number set up as your pk.... Is it ever possible that the number could change? Like a ref could be assigned a new number? Say, the number is generated from some other software and the software is switched and so the ref gets a new number, you would have to either copy or reenter their information, and it would effect any references to that number, etc.... I would suggest always using an autonumber as your PK, it does no harm, ever as far as I know, and only helps to prevent unkown issues.

 

[Kryst51]

Another thing is in your referee marks table, it needs its own unique ID. The way you currently have it is going to cause sever problems later.

 

[Dannyboy11]

Hi

I set the primary key because that is the referees NPD number, which is their unique identifier.

I do agree with you though, in that it would probably be best to give them their own unique number within my database so I am in control of the PK.

If I now create an autonumber as a PK, would I have to redo all of the queries?

 

[Kryst51]

Also read my blurb about not using table level lookup fields. This too causes lots of problems.

 

[Kryst51]

Also, the way your address are setup in the referree table is a little unnormalized. Consider creating an address table that has its own pkAutonumberID and have a foreign key in the address table that will reference the referee pkID.

 

[Dannyboy11]

Ok, I've read about not using table lookups - what can I do instead?

Thinking about it, yes I agree with the address not meeting normalisation rules so a further table would be better?

I imported the referee details from an Excel spreadsheet so is there a quick way to just transfer the address fields to a table? There are just under 500 referee details.

 

[Kryst51]

OK... Now to your form. It isn't bound to anything. It should be found to the query you created.

I have attached an ammended copy of your DB, I had to fix two queries to make this work, qryRegistered9v9 ( I just added the autonumber primary key in) and qryAverageRefereeMark (I changed the relationship from the other number you were using to the autonumberID. I also changed the table references.

 

[Kryst51]

Hi

I set the primary key because that is the referees NPD number, which is their unique identifier.

I do agree with you though, in that it would probably be best to give them their own unique number within my database so I am in control of the PK.

If I now create an autonumber as a PK, would I have to redo all of the queries?

Yeah, but it shouldn't take too much work. but its better to get table structure nailed down BEFORE you continue with anything else.

 

[Kryst51]

Ok, I've read about not using table lookups - what can I do instead?

Thinking about it, yes I agree with the address not meeting normalisation rules so a further table would be better?

I imported the referee details from an Excel spreadsheet so is there a quick way to just transfer the address fields to a table? There are just under 500 referee details.

You can use queries and combo boxes on forms for your lookups.

As far as how to import the addresses, try asking that as a separate question AFTER you normalize your table structure.....

A.) Remove All table level lookups
B.) Set up your tables so that they are normalized.
C.) avoid spaces and/or special characters (I would even go so far as to not use any numbers in ANY of your names)
D.) Makes sure EVERY table has an autonumber pk ID. Then fix your foreign keys to reference this number and fix any relationships you have set up in your relationship screen and queries.

 

[Dannyboy11]

Ah ok thanks. How do you bound forms to queries as I'm guessing I'll need to do this often.

I'll continue to work with your attachment and I really appreciate it

 

[Kryst51]

Ah ok thanks. How do you bound forms to queries as I'm guessing I'll need to do this often.

I'll continue to work with your attachment and I really appreciate it

Either use the wizard that comes with access, or in the form properties, Select the data tab, and activate the record source property, there will be a drop down list that contains all the tables and queries in your db.

Again, Good luck, and don't hesitate to post back if you have problems.

 

[Dannyboy11]

Hey

I'm going through your steps of normalisation.

I have a question about foreign keys. On tblRefereeMarks you have put fkRefereeId - is that obtained from anywhere or would you just type it in? What happens if you enter a referee id that isn't actually a referee id as nothing stops you (which is why I thought table level combo boxes were good as you could see what was valid).

If you look at tblAvailableDates (I know I forgot to remove names...) would that be pkRefereeDatesId, fkRefereeName, and then each Sunday of the month? Or does having each Sunday break normalisation and, if so, how would I get round this problem?

 

[Kryst51]

Hey

I'm going through your steps of normalisation.

I have a question about foreign keys. On tblRefereeMarks you have put fkRefereeId - is that obtained from anywhere or would you just type it in? What happens if you enter a referee id that isn't actually a referee id as nothing stops you (which is why I thought table level combo boxes were good as you could see what was valid).

If you look at tblAvailableDates (I know I forgot to remove names...) would that be pkRefereeDatesId, fkRefereeName, and then each Sunday of the month? Or does having each Sunday break normalisation and, if so, how would I get round this problem?

First off, you won't be entering your info into tables. You will be using forms for that, and your form controls, what can and cannot be entered. So you can have a form for a referee. then a subform for marks if your tables and relationships are set up correctly and the wizards used correctly then this will not be hard. The linkage will be done automatically.