Query Criteria Question, Quick Answer Needed
Aug 23, 2006
Thanks for helping me out with this question. Im sure this is pretty easy to do. I have a query with a few paramaters set up... one of them runs a custom function all i want to do is this
query one
Average water flow:Avg([WaterFlow])
when run this just shows me the average water flow for the database... now if i have another criteria i want to base off the average how do i get the query to realize that. for example it would look something like this
[Average water flow]/[currentFlow]
how do i reference another criteria? hopefully this isnt to hard to understand.
View Replies
ADVERTISEMENT
Sep 28, 2006
Text field
"CM#000001"
How do i write a where criteria to exclude all records that start with "CM#"
like "CM#*" does not work
like 'CM#' & "*" does not work
thanks
sportsguy
View 3 Replies
View Related
Jun 15, 2006
Didn't know where to post this one...... But forms seemed logical..... I am doing some experimenting with using a barcodes. I can create them now, no problem. My next step is using a scanner to input to a textbox...... Problem isn't with the forms..... But more with the scanners. I thought if someone has done one of these DB's they might know.
So........ I need a cheap scanner to test with.... Found a few on Ebay. The cheapest being a Symbol- "Hotshot" LS2100-I000.Says it has "DB9" plug. The ebay ad says "comes with NO software". Only thing I am wondering about are drivers. I've read in a couple places claims that this scanner "Needs NO drivers"
So my question is.... Would this scanner work with Access on a Windows XP system? No drivers needed?
Hope this question isn't too "off the subject" But I am dealing with Access and hoping someone has done this.
Thanks
Curtis
View 3 Replies
View Related
Sep 12, 2005
hi people. Im trying to make a query where the query will search for a specific word in the field, e.g. search for "debt cleared" in 'Other Notes' field. The other notes will also contain other info but i would to search for a specific word(s). Can some one please show me what kind of criteria to put.
Thanks.
View 3 Replies
View Related
Sep 10, 2007
I have a querie made to run a report. Criteria in the querie is
[Enter shift number 1,2, or 3 to view shift]
This filters for 1,2,or 3. what would I need to add to run all 3?
I would like to run all 1,2,and 3 or just 1 or 2 or 3.
Thanks
View 3 Replies
View Related
Dec 27, 2005
Hi all
I'm currently working through developing my first database.
Basically, what I would like is for a query to run from a command button.
The query should look at a field in a table (a yes/no field) and report back the ones that have been filled in.
I know how to run the query from the access 'Query' window, BUT, I would like this query to run from a click of a command button on a form.
I've looked it up in a book, which explains about SQL, and didn't really understand it. I've also searched on here, but people talk about Macro's, and I don't know anything about them.
Can anyone help?
Cheers
View 2 Replies
View Related
Jan 13, 2006
Just a quick question.
is better to use a query for a form or just link straight to table.
e.g you have tblColour, this table has ColourID & Colour.should you build a query including all fields then build a form from that query?
Thanks
View 2 Replies
View Related
Nov 9, 2005
I have a table with 3 fields... ID, Name, Hours
ID is NOT unique in the table
IDs Can have different names
Hours is what I will use to find the total hours
I want my query to group by ID only, and show the SumofHours
BUT... I want to show the name as well(without Grouping by it), I want my query to Show me the name that is MOST Recurring.
So lets say that ID '1' occurs four times, with the Names...
'Mark' 'John' 'David' 'John'...
Running the Query I would want to see 'John' under the name field Right Next to the now Unique ID '1'
At first I thought I could just use the Max function from the drop down menu, but that only uses the one that is alphabetically a maximum... I need it to be a maximum by count..
Any help would appreciated,
Ayyad
View 2 Replies
View Related
Jul 29, 2005
I have a query statement:
Final Tax Free Amount: IIf(Left([Final Tax Code],1)="k",Right([Final Tax Code],Len([Final Tax Code])-1)*-10-9,Val([Final Tax Code])*10+9)
If I wanted to include: If final tax code = BR or NT then = 0. How can I add this to this statement?
thanks
View 1 Replies
View Related
Jan 15, 2006
hi there
could somebody help in the query design in order to query table 1 (image attached), by showing the maximum weight for each component_id, but also include other details such as the date when the maximum occurred and the event_id, the result as query 2 (image attached)
if i just select component_id and weight, i can do this by selecting max weight, but when i add the date and / or the event_id columns then i get duplicates in the component_id column
any help would be greatly appreciated
View 1 Replies
View Related
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
Apr 12, 2005
I have a basic Access Form that contains a small number of questions.
Question 4 is a Yes / No combo box
If the user enters Yes, I want the focus to go to Question 5
If the user enters No, I want the focus to jump to Question 4.1
Completing question 4.1 will take the focus to question 5
I'll be hornswaggled if I can figure out how to do this.
I would love someone to help me out!
:confused:
View 12 Replies
View Related
Oct 31, 2006
I have a receipts table with columns- Customer ID, Receipt #, Amount Paid, Date and in another table- customer Id, First name, Last name, Contact # ,Total Cost. How can I get the balance owing by a customer who have made several payments.
No question is stupid
View 1 Replies
View Related
Nov 19, 2006
Hi,
I was wondering if i could get some help here. I got problem while trying to perform sql query. I got an error message saying
"No value given for one or more required parameters".
I think i know what caused this but i just don't know how to get around this.
The query I'm trying to perform requires 3 tables and 2 queries.
Tables: Projects, Plan_Names, Reason
Queries: PSQuery, ProjectQuery
I think this is due to the fact that those two queries are empty for a start. Hence, it needs more value.
My question is how do i in the code, tell it to execute the two queries before attempting the query i want to perform.
Thank you in advance
View 7 Replies
View Related
Aug 28, 2005
I have a query where I need to find out the average score of employees over a date range. I have a field called date where I enter a date range (Between [START DATE (MM/DD/YY):] And [END DATE (MM/DD/YY):]) and another field where I enter the name of the supervisor. Now the table I have created contains the scores on a daily basis. So what I want is that when I enter the date and the name of the supervisor it should give me the average of the scores over that date range of all the employees under that supervisor along with the name of the employee.
View 1 Replies
View Related
Sep 6, 2006
I have two tables, a Property table and a System table. They are linked so that there are many Systems linked to one Property.
Each System has a risk score and these vary within each property. My Property table has a Property risk score field and in here i would like the maximum System risk score to go.
I'm just wondering how i would do this using a query? All the system risk scores have been entered already.
Any information would be great!!
Matt
View 4 Replies
View Related
Jun 11, 2007
Hello everybody,
I am unsure whether this is best done in SQL or VBA, however, I do believe it is possible in SQL so I will put it here.
I have a field 'value of purchase' in which an items cost is inserted. What I am wanting to do initially is automatically deduct 20% off the value of purchase every year until it reaches zero. Now I don't want to deduct 20% off the new value every year, for example:
The price is $1000, 20% deduction is $800. But then year 2 it will be $600 then the next year $400 and so on. So I suppose the first year 20% is taken off the original price, then for year 2 40% and so on (always off the original price not the newly found values).
I want to display this in a new field 'current price'.
I am just not sure how to create the query so it will automatically deduct these amounts per year. If anybody has any advice/help it is muchly appreciated.
Thanks :)
View 2 Replies
View Related
Aug 20, 2007
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
View 2 Replies
View Related
Jan 15, 2006
Hi all,
Just put some buttons together, and I notice when I click on them I get a box appear on the button itself. Strangely, some of the boxes are solid likes and some are dashed.
Just wondered what they are, and how to change/get rid of them.
See sample below
Thanks
Col
View 4 Replies
View Related
Jan 29, 2007
Hi, totally new on MS access. Is it possible to solve this following task in Access. (using it in Excel today)
Ex.
2d table?
Prod 1 Prod 2 Prod 3
Prod 1 0 10 20
Prod 2 10 0 30
Prod 3 20 30 0
Question 1: What did you just create?
Answer: Prod 1
Question 2: What will you create?
Answer: Prod 3
Then Access will show 30.
Probably explaning it bad but hope you understand and can answer me if this is possible to solve in Access....
The mission is to create a database with setup times between diffrent products, and make it possible to ask a question to get the answer.
/Martin
View 11 Replies
View Related
Feb 3, 2007
I've been here for two months and answered over 400 questions. I'm seeing a pattern here, and I have decided to post the best ways to make sure you are never answered.10) Blood, sweat, and tears are for losers. Spend no time figuring things out on your own.9) Announce almost ceremoniously that you have no VBA experience.8) Fail to use the search functions of this forum.7) Try to put every example you're given into a macro.6) Believe that this is the only source for answers, because Access Help provides nothing. Nothing, I tell you!5) Have us write your homework for you and then complain when you don't understand it.4) Post in German.3) Have a belief that your particular problem is the most important issue ever, and post in every sub-forum possible. Then bump it.2) Be vague, and then be belligerent.1) Have no appreciation for the people that are answering your questions in the first place. I know this sounds cold and mean and otherwise uncaring, but we are here to answer questions, not give you fish, as it were. This is a place to say, "I've done most of the work, but I need help over this hump," not "I have this crazy project/idea and I want you to write it for me." I've written over a dozen example DBs for people, and I know that I know what I'm doing. But, myself along with perhaps the majority of others that answer the majority of the questions, I'm asking on their behalf to please take the time to figure some of this out. If you can't do VBA, you are using 25% maybe of Access. If you can't understand your own DB, then asking us to fix it is a solution, but not a learning opportunity for you. If you can't understand what I'm getting at, it's that so many posts are the same. Again, I'm not trying to be an ass, but if you want to be a decent Access programmer and a programmer in general, then please, take the time to learn it. There are plenty of books (look at the bottom of this forum) and there are plenty of resources. I don't think any of us are teachers by trade, but the best teacher for something like programming is always trial and error. Programming is a language, a passion, a "something you figure out" if you will. If you're spoon-fed the entire time, you don't learn. I will continue to answer as I always do and I apologize if anyone was offended, but jeez, make sure you've tried everything offline before asking for help. And don't forget to search first, both here and Google.Sorry for the long read. :)
View 14 Replies
View Related
Nov 12, 2007
Hi Guys
I currently have a database and the powers at be want me to enable a button that will count how many times in the database a particular month is used. So for example how many times did we visit a particular place in September/october etc.
Is there a way of doing this that i can't see, I have tried searching the forum but to no avail.
my thanks in advance
Anthony
View 7 Replies
View Related
Oct 13, 2005
Please Help. I am tring to get the Month to date, year to date and week to date total in a query. I added a parameter for a date range. Instead of giving me the mtd, wtd, ytd, I get the whole months daily total. Can someone tell me what query caculation do I need to just get one number instead of daily numbers?
View 2 Replies
View Related
Apr 19, 2006
I have created a basic database but now need it to perform some difficult tasks (Well difficult to me) an havent a clue how to do this.
I need to be able to search the database for a vehicle type for a specific dat to see whether it is available or not?
and i need to be able to produc a list of vehicle & drivers that are not booked by entering a date?
Has anybody got any ideas on how to do this because its racking my brains
Thanks
View 4 Replies
View Related
Aug 30, 2004
Why so many repeats of the same answer
Thank you MWalt for your reply about finding the stock in hand. It works, sort of. It gives the answer but I end up with each item being repeated about 141 times. So instead of ending up with a list of about 140 items, I have a list of 20000+ because each item is repeated. I'll include the code of the first query that you suggested called qryNotItem...where do I go from here.
This part works, but when I do the second part I run into an error.
SELECT [Equipment Inventory].EquipmentID, [Equipment Inventory].[OT Equipment type], [Loan details].LoanOccID, [Loan details].[Return date]
FROM [Equipment Inventory] INNER JOIN [Loan details] ON [Equipment Inventory].EquipmentID = [Loan details].EquipmentID
WHERE ((([Loan details].[Return date]) Is Null));
Thanks
View 1 Replies
View Related
Aug 20, 2004
Hello, I am new here. I am practicing with a Certiport CD
to take a MOS exam and I am having a hard time with one
question on the practice test. The question seems to be asking
me to match output items to input items. There are four items
listed in middle column - they are: datatable, mailing list, report,
and form. I am supposed to (I think), move items from the list
that I think are "input" items in a left-sided column labeled
input, and put the items that I think match the items in the
input column, in a right sided column labeled "output". The middle
column would be left empty and the side columns should have
items that match. For instance, I have been putting "form" in the
input column, and to match it, I put mailing list in the output
column (you can get mailing lists from forms right??) No matter what
I do, it says the answer is wrong. Am I doing something wrong? I
tried all the combinations I can think of and nothing works. I called
the company that made the CD and they said they could not help me.
Could I be matching up the four items incorrectly. Could the question
be worded improperly? Anyone who can help me, please do. Help! thank you.
View 9 Replies
View Related
