General :: Possible To Have A Query To Calculate League Table
Sep 5, 2013
I have created a database that records the scores of each player etc and puts them in league table like structure.
Within this database which I would like to extend I would like to know if its possible to have a query to calculate a league table for the teams if home team overal score is greater than away team home team get 2 points for win 1 for draw no points of loss and same if away team won.I also want to be able to change it to a web database and add club address details like contact list to it.
I am trying to create an append query that will update employee time cards for holiday hours without having to enter each time card individually.
* I have an employee table which has the employee id, first name, last name and so on.
* I have a timecard table which holds the employee id, date and time card ID.
* I have a time card hours table which holds the time card detail ID, time card id, work order id, job code id, reg hours, ot hours, holiday hours, etc
* I have a time card expense table which holds the time card expense id, time card id, work order id, inventory item id, cost, total, etc
I need to create a form with a multiselect list containing a list of the employees and a box for the date and for holiday hours. I need to be able to select the employees, enter the date, enter the holiday hours and append it to the time card table AND the time card hours table.
For example I need to append and create new records from the form: the employee id (from the multi-select combo list) and date (txtDate) would pass to the time card table and the holiday hours (txtHours) needs to pass to the time card hours table.
I cannot for the life of me figure this out...it doesn't seem like it should be too hard.
is it possible to create a league table on those results.
1. There is more than one competition in the table so it would have to be by compID 2. There is fixtures without results so they would have to be excluded as well.
I am trying to write a function in an access 2007 query that will calculate the date range for each of my records based on the month-ending date and the In-transit days assigned to each record (excluding any weekend days) (i.e. if the in-transit days number is 5 & the month ending date is 5/26/12 then my date range would be 5/22/12-5/25/12). I want the funtion to calculate the range, I cannot use parameters as each record will have a different range based on it's in-transit days .
I am trying to create an attendance database, our company introducing the point system attendance,
Called Off (CO) 2 points Left Early (LE) 1 Point Tardy (Tar) 1 point
Employee can reduce point if they have perfect attendance for 90 days from the last day of violation. For example, an employee absent on 01/01/2015, he will received 2 points, the credit will giving on 04/01/2015, if there is no violation, but if he absent again on 03/31/2015 not only he will received 2 more points his 90 days will start from 03/31/2015, now he will eligible to get credit on 06/29/2015 and so on.So far I have created 3 tables and 1 query.
Tables
Employees: Id, Last Name, First Name Points: Id, Description Points Attendance:Id, Date, Employee ID, Points ID
Query Date Employee ID Last Name First Name Point Description Points
how and which formula to use which calculate the points based on above example.
I am struggling with calculate difference between two query table values, I first created a make a table query(current meter reading) which contains one column called "meter read" , and I created a second make a table query(previous meter reading) which also contains "meter read" column, I linked those two make table queries to calculate the value difference between two date, how can I create a form to calculate diff between any two date?
I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:
The Query is called KatiesPeriodTakings The field I want totalled is called Price The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
Hi everyone, im new to this forum as well as MS Access. Over the past week or so Ive been reading through a lot of tutorials and watched a few videos on how to master Access. I've learned a handful of things, however its not enough, so i need some help.
Im trying to create a simple database for our local community football/soccer league. It's nothing fancy, just simple information to keep things tidy and organised.
The main features include Keeping a track of all results and outputting an up-to-date league standings, so it can be printed and available for everyone to see.
Few details: in soccer, there are 3 possibile outcomes of a match (win,draw,lose). Each win earns a team 3 points, draw 1, lose 0. Of course, the standings table will be sorted by the amount of total points each team has uptodate (descending order). Oh, and the goals scored - goals conceded should also be summed up for each team and displayed.
In theory, it sounded easy to me, but when i actually started doing it, i got stuck right at the beginning... :confused: So, here's what i got so far: tblResults table that collects the following information: Date, TeamAName, TeamBName, TeamAGoals, TeamBGoals (the last two collect how many goals team a and b scored)
...well, thats pretty much it... lol :o
I've been experimenting with queries for a while but feel without some help i wont get it sorted... Would appreciate if anyone could point me in the right direction... :)
Hi does anyone know of a system out there in MS Access that can do what this program does in word. i have been using this word version but i wanted to keep a history of records...
I use this for Badminton and not squash, so if anyone wants a game i am happy to play
anyway have a look and let me know how hard this would be to achieve.
I've been asked to collate some information based on a kid league, I am trying to sort out how precisely it works
the data I have is this
finish position, name, club
I need to calculate the best kiddie based on 3 of a possible 6 races
So tbls
tblResults fldSeries NAME CLUB Pos(isition)
tblScores fldID pos Points
query1 SELECT tblResults.fldSeries, tblResults.NAME, tblResults.CLUB, Sum(fldScore.Points) AS SumOfPoints FROM tblResults LEFT JOIN fldScore ON tblResults.Pos = fldScore.Pos GROUP BY tblResults.fldSeries, tblResults.NAME, tblResults.CLUB, tblResults.Pos HAVING (((tblResults.fldSeries)="220-2005") AND ((tblResults.Pos)<51)) ORDER BY Sum(fldScore.Points) DESC;
I need this to be changed to only pick up athletes with only the best three scores?
I have a race league, I want to select the top 10 point scores for each member.
I have read the Allen Browne article (and many others) and tried many variations on his code but cannot get this working.
I face two issues - The ORDER by clause has no effect, points are not sorted with largest first - Access being unable to differentiate between scores with the same value and returning additional records. I have added an "Event" field to make the record unique, but this does not seem to work.
Query code is
SELECT qLeague.Member, qLeague.Event, qLeague.Points FROM qLeague WHERE qLeague.Points IN (SELECT TOP 10 Points FROM qLeague AS Dupe WHERE Dupe.Points= qLeague.Points ORDER BY Dupe.Member, Dupe.Points DESC ) ORDER BY qLeague.Member ASC, qLeague.Points;
This returns more than 10 results per member:
Member Event Points Alex Peters SDMC North Weald Sprint 3 Alex Peters HCAAC Debden May 3 Alex Peters GB/Harrow TAMS NW Sprint 4 Alex Peters HCAAC Debden Sprint 5 Alex Peters Llys y Fran Hillclimb 6
I run a soccer league where we track players attendance for each game. I currently do it on a spreadsheet where each game date is a column and each player is a row. We also track which team they play on at each game (they can play on different teams different weeks). I currently have a second tab in the spreadsheet to record which team a person plays on each week.
Setting up a table of fields for this is relatively easy. The problem comes to data entry. I want to be able to visually see the data like I can in a spreadsheet (names in rows, dates in columns, intersections containing either team name or whether attended) and whilst a cross-tab query gives me the layout, I cannot input data in a cross-tab query.
i have 3x time fields in a table i.e time received, time started, time completed i need to accurately caculate the time between time received and time completed. whenever the time goes past 00:00 it seems to add 24 hrs to my calculated time is there a macro or code that i can use to rectify this problem?
I have an birthday report for the current month (august) and would like to make a button on the report that can calculate next month (september) and shows birthdays for that month. how to calculate next month?
Here is an explanation of my database and what I am trying to accomplish. The tables and their fields I have in the database are as follows .....
EmployeeT EmployeeID - Primary Key - Number (not an autonumber because they are county assigned) LastName - Text FirstName - Text EmailAddress - Text JobTitle - Text MobilePhoneNumber - Text Notes - Memo
[Code] .....
All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either "ES Building" or "D3" when inputting the data).
For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.
Currently using a lot of Power Pivot Tables which in part calculate "averages" - but when the data comes form a Query that contains a calculated field using the iff() funtion such as: Outcome: iff(Status = "P", 1,0) we end up with a lot of zeros that create a problem in calcuating a true average. In the following example 2,3,0,1,0,0 the average is "1" but if the zeros were blank the average would be "2" because the blank cells would not be counted.
Is ther a way to have something like: Outcome: iff(status = "P",1,"blank") but what I want is ether 1 or a blank cell, NOT TEXT. If we use "" for the false outcome we still have cells that look blank but are in fact text cells and are counted in calculating average.
Is it possible to set an access database to take a depreciation value of lets say 10.00, and subtract this from the current value every month?
So far my database has a form where you can input the depreciable life, purchase price, current value and total depreciation. However the date and amount of depreciation has to be added manually into a data table for the text boxs to calculate how much it has depreciated and the current value.
I'm trying to show the modal value of a column in a query that only has text in, not numbers. I want to present this in a text box in a report.
My report already has a query as its record source that only shows data for a given year and given month, these are chosen using parameters created in the query. Thus all I need to figure out is how to show the modal value for the column "Categories" on the report somehow.
What I'm looking to do is calculate the success rate (%) of compliance rates with Quality Control paperwork. Essentially, each record has 12 "check box" fields representing the different QC sheets that are submitted each day.
I have one table with employees and positions. Each position has a specific rate which is stored in another table (table2). Since the rates of the positions are changing, I want each time the employee get paid with the correct rate.
The problem is that I do not want to lose historic data since I want the rate to calculate the salary. I thought that the best way is to look up for the rate according to the position and the period that is valid.
I have attached herewith a small example with the two table that I want to use the relationship.
