I think this would be a simple query but I have not been successful in getting anything to work at this point. So I thought I would through it out to the pro's here! Thanks for any help you might give.
Table looks like this:
EventID EventDate Score EventType
46 09/20/2007 72 1 Day Net
46 09/20/2007 72 2 Day Net - Day 1
46 09/20/2007 68 1 Day Best Ball
48 07/14/2007 69 1 Day Net
48 07/14/2007 34 Nassau
What I am trying to get from this data is the AVG score, but not include Nassau or Best Ball events. So I have used the Not Like "%Nassau%" etc, in ASP (since ASP requires % rather than * like access. The results I would like to see are one score from each DATE avg'd. That means with this sample data we should get...
46 09/20/2007 72 1 Day Net
48 07/14/2007 69 1 Day Net
Avg score 70.5
I don't care to have the Event Type listed, if that causes grouping issues. I just am not getting anything to work. Thanks for any help.
In short If I could get the avg score using only one score a day that is not a Nassau or Best Ball event, I would have to rejoice! Thanks so much.
I have a form based on a table with three controls: txt1, txt2 and txt3. Txt is unbound, while the first two get their source from the table. I would like that if txt1 is showing data, then txt3 shows the same data of txt2. If this possible? I have tried something like this but does not work:
If IsNull(Me![txt1]) Then Me!txt3.Visible = True
If Me!txt1 = "*" Then Me!txt3 = Me!txt2 End If End If
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"
how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?
Select Materials.ID, Materials.AvailableUnits, Substances.Name, Suppliers.CompanyName FROM Materials Right Join Substances on Materials.Substance = Substances.ID
This works in joining the substances.name (from substances table) with the value stored in materials.substance (which is ID of substances.ID)
How can I add another Join to join Suppliers.CompanyName with suppliers.ID. The current Materials.Supplier is a key of Suppliers.ID which holds the value of Suppliers.CompanyName. I've tried this a million times and as soon as I insert a second join, no matter what format I get "syntax error in join statement".
Okay, now I'm suspecting there's more to this than an overly complicated control source statement....
My summary report contains multiple objects that calculate totals based on a statement in their control source. The report ran fine for a few weeks but all of a sudden it started producing this error:
"The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I thought perhaps that 12 lengthy control source statements were simply too much for Access to handle, but then I did some testing and eventually removed all but this control source statement: =Sum([NotifType]=9)
That's about as simple as it gets, but when I try and run the report I still get the error. Ggggrrrrrrrrr....
Can anyone tell me what causes this annoying error and how I can get rid of it? (I already compiled.) What little Access quirk am I running into? I will gladly zip the file and post if necessary.
I have an open form with buttons to open other forms and use an If statement to position them an example is below.
Code: Private Sub Command31_Click() DoCmd.OpenForm "Opener", acNormal If Me.Frame54 = 1 Then DoCmd.MoveSize 2500, 1000 ElseIf Me.Frame54 = 2 Then DoCmd.MoveSize 25500, 1000 End If End Sub
This works perfectly, however I need to put similar code as a line in a macro and cannot seem to link it.
Code: Public Function totalmove() If Forms.Scan_Data.Frame54 = 1 Then DoCmd.MoveSize 2500, 1000 ElseIf Forms.Scan_Data.Frame54 = 2 Then DoCmd.MoveSize 24000, 1000 End If End Function
I get Error 438 object doesn't support this property or method...
Hi there, I have a really simple question. I have a field in a table that contains comments (text format). I want to run a query where I can get only the records that contain the word "high" in the comments. There may be some typos so I would like records returned that also contain something like "high". High is not the only word in the comments.
ItemType is a list of items (Table, Desk, Bookshelf, Chair) Style is a type of style for the item (Circular table, Radial Desk)
I want to create the Items.Style field based upon a query of the StyleTypes. So if a user types "Desk" in Items.ItemType then the Items.Style field will be limited to only those Styles which match that selected item within the StyleTypes table.
Think thats clear enough, but my attempts have all failed, hope you guys can give me a push in the right direction, thank you!
I have very little knowledge of databases and even less about writing queries.
The problem: I have a database that lists cities (such as Paris, London, New York etc) as rows and employment status (Full-time, Part-time, Unemployed etc) as columns. I need to go through each city and record which employment status is greatest (for each city) and present it in a new column. I can do this manually but I’m sure a query can be created to automate the process.
Hope that all made sense. Can anyone suggest ideas on where to start?
hi have just designed a rather simple music database for my dad to use at home as he has large collection. have a table with the heading"artist" and another one with "album" ...my problem is that on the table there maybe 1 or more instances with the same artist but with different albums...when i run a simple query for ..say artists starting with the letter"a" i may get about 10 cases of the same artist appearing in my query when all i want is one...(depending on if i may put in say one artist like abba having 10 albums etc)...i am entering each artist with the album each time so i may enter the artist in say 10 times....depending on how many albums etc
if you understand all that...i would be grateful if someone could help me with a simple query alan
I have a query that returns the uncompleted jobs from a single table ( its a calls database) all i need is query that returns the amount of uncompleted jobs onto the switchboard I.E whatshername has X amount of uncompleted jobs , i have done a search and most of the solutions are very complicated for what i want to do .....anyone any ideas ?? pls
SQL and Access are both new to me. I have purchased some books (which are still in the mail), and have gone through this forum (which seems to be a little more advanced for me at this moment).
I have a database that contains Business_Unit (Store Numbers), Post_Date (Last Date of Inventory), and other misc. stuff. Anyways, My goal is to pull the latest date of inventory for each individual store before the last ninety days. What I have so far:
SELECT DISTINCT informix_shrink_head.business_unit, informix_shrink_head.counting_event_id, informix_shrink_head.post_date, informix_shrink_head.delta_cost, informix_shrink_head.delta_count, informix_shrink_head.count_reason_cd, pcw_loc_master.closed FROM informix_shrink_head, pcw_loc_master WHERE (((informix_shrink_head.post_date)<=Date()-90) AND ((pcw_loc_master.closed) Is Null)) ORDER BY informix_shrink_head.business_unit;
However, I get the same store multiple times and for multiple post dates. I do not even know if this can be done in one simple swoop, or if I need to create another table and re-query that? Any assistance or comments would be much appreciated.
I have a database that collects information on products when engineers return them, what I need to do is when they have submitted the form, email it to 3 people so that the paperwork can be raised.
I understand that I will need to create a query to display just the last record and then email it.
So the idea is when a record is created and the engineer clicks submit the last record then gets emailed to the three people.
Please be gental with me im still quite new to all this.
How do i create a query that just displays the last record?:confused:
I am sending ZIP file in attach with files in it. I would like some help to change the 'Q_test' query so that I can get the result shown in the excel file.
I want all the records to appear which have some text in that particular field. So if there is no text in that field, it won't come up. What would I need to write under that field in the query?
I am trying to run a query to retrieve all rows in all tables using a row called Project Status. I am however not able to retrieve any data what so ever with the wizard and by using the limited sql I know. I have attached a copy of my tables if it helps.
The relationships are as follows: Release to PCR - One to many, Sponsors to PCR - One to many. The column i want to filter on is Project_status located in the PCR table.
I am re-visiting access for the 1st time in a couple of years to develop a quotations system and I am just messing around at the moment with Northwind trying to work out how it all works.
I have come across a problem that is baffling me....
When displaying the order details table (referenced from products), the product ID is disoplayed in text form, not as a number. Whenever I try and do this with 2 similar tables using the query builder I always get just the other tables number. I have no idea what I'm doing wrong as every field, attribute, join etc seems to be just the same as the sample tables.
I apologize, I know this has been covered. But I just spent half an hour reading old posts and still can't quite decide how to apply it to what I'm doing.
I have a db that logs surgeries and all their details. One of the new things they want to do is be able to run a list of average cost for a certain surgery, since patients are always asking ahead of time how much it will cost. I have a query (and report that runs from it) that will list all the surgeries and total charges for individual ones for a date range the user specifies. But I can't figure out how to make it calculate an average charge for each surgery. I could if there were always a certain number to divide by, but of course there could be 2 of this type of surgery and 57 of that type.
A friend of mine has recently created a simple database which holds details of childrens story books. It logs the name of the Author, the storyline, its character etc in various tables...
He's currently creating some simple queries for his database. However, there is one query which neither he nor I can figure out, I'll try to explain to the best of my ability.
Basically, the 'story line' field in his database explains the basic plot of the book, some of the plots have the words "son", "dad", "father" and "boy" in them.
Now, he wants to create a simple query, which looks up and displays data which contains the keywords "dad", "father", "son" etc in them. So far he's tried...
Like "*Son*" Or Like "*Father*" etc...
This worked, briefly. The query was bringing up storylines with both contained the words "son" and "father" , which is correct. However, the query was also bringing up story lines with the word "song" in them, or any word which remotely related to one of the keywords, but didn't contain the exact criteria in the query (son, father etc).
In basic terms, how do you create a query which only brings up words that match the EXACT query criteria? In this case, 'Son' or 'Father' or 'Dad' etc? Rather than words which only briefly relate to the criteria, such as 'Song' or 'Fat' etc?
Thanks for your cooperation, feedback and examples would be highly appreciated.
