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 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.
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... :)
I m trying to find a way of automatically inputting the progress of my project.. it's confusing so i'll try ot explain more. I have a project plan broken down into weeks . Each element of this has a schedule and complete date for various different milestones which indicate what week my project is sitting at:- eg: I have reached this milestone therefore my project is at week 10 and so on until completion.
Is there some query I can run that can do this automatically?
hi all - i know there are many threads relating to linking pictures to records etc, but forgive me for asking one more time:
I have managed to get pictures into my dbase - using Microsoft KB help - by linking them in a table to an external source.
However, I want the dbase to know which picture is for what details. eg. I have a cars sales dbase. I have all the pictures in one folder of all my cars which are linked to the dbase. When I pull up car details in the form, I want the picture to come up aswell - the correct picture.
Pls let me know if this has been done. Sorry for extending this topic; however, I could not find a "simplish" soln. to this. Thank you once again :o
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.
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 developed a query that produces a list of forms and an effective date where the form may be applicable. But now I am stumped. I need to reduce the results to a specific state (which I’ve done and it works) and for a specific date (the stumped part). Let’s say user needs 2/15/2005. I think I can eliminate forms with effective dates greater than 2/15/2005 (not quite sure how yet) but I am perplexed how to eliminate certain forms that are no longer valid because they’ve been superceded. In the sample table for CA, I would expect the following forms retuned and no others: CG0001 1204; CG0002 1204; CG0009 1204; CG0033 10 01; CG0034 1204; and CG0035 1207. The expected results for the CT column would be different (though some would be the same).
Any advice to point me in the right direction would be appreciated.
I have a form with two subforms. One of the two subforms works perfectly. The other just gives me a grey screen (like there's no recordset).
The two forms are very similar and I've checked everything I can think of... Here's what I've done. If you can think of anything else to try, please let me know - This is driving me crazy!
1) The subform works independent of its parent 2) The source object for the subform is set properly 3) The child and master fields are set properly. Data types are the same. 4) The record source for the form (used as subform) is set to the correct query. 5) The query used has been run independently with no problems 6) The form has a bound text box with the proper control source 7) I've tried placing a breakpoint at the first line of form_current.. never gets hit.
I don't know why it's not working and there's nothing that tells me what/where the problem is. I'm out of ideas on this one.... Any help is greatly appreciated!
StartTime = Format dd/mm/yyyy hh:nn:ss ExpFinishTime = Format dd/mm/yyyy hh:nn:ss Dentist = Number
StartTime is bound to my table, ExpFinishTime is calculated in the forms underlying query.
Before the record updates I am trying to check if anyone already has an appointment scheduled in during that particular time for that particular dentist. So if the record being added has a starttime between those two times for that dentist, throw up a message box.
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.
Here is where I get lost. Each item has a variable for each month So item 1 January = .01 February = .03 ECT. Once set the Variables rarely change, at most once per year.
I will need to be able to pull an item and based on what month it is pulled uses the variable to multiply several other factures.
My first Thought was,
tblVarItemList IDVarList FKToItemList VarJanF VarFebF VarMarF --And so on--
It just does not look right. Any Suggestions on what might work Better. :confused:
I have some simple validation that unit price is greater than 0. The code is this:
If Me.txtUnitPrice.Value <= 0 Then MsgBox "Please enter a value greater than zero", vbOKOnly, "Alert" Me.txtUnitPrice.Value = Null Me.txtUnitPrice.SetFocus End If
However, when I run it, the setfocus doesn't work. It jumps straight into the next field. I can make it run to any other field (productname, productID). But not back to UnitPrice.
Hello, When I try to run any query that contains a specific table I get a message a Not enough memory error. I can open that table which, contains 47 thousand records and view that data unless I get to one record then the entire database crashes. I have tried to Compress and Repair and the database crashes. When I say crash I means it completely disappears and I get the send error to Microsoft Message. I can not select record due to it crashes when I get to it. I have also tried selecting record by going to one above the quickly scrolling to a record way below it and it errors and crashes. I have tried to delete it using a delete query and it only returns “Invalid argument” when I try to select or delete.
ANY suggestions or ideas would be greatly appreciated.
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
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.
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.
How can I export three tables into a single excel workbook.
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type 12345, 67890, 1 12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345 12345, 54321 ...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:
one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
