Query Criteria Help?
Jan 31, 2006
hi,
I have to put a report together to send to our personnel department in regards to some one to one training i have been doing.
I have a database with the following fields:
Forename (text)
Surname (text)
basic training (yes/no)
basic training cpmplete (yes/no)
basic training date (date/time)
advanced training (yes/no)
advanced training date (date/time)
advanced training complete (yes/no)
The report i need to send must contain information on name, the type of training completed and the training date.
I have to send the report each week and it must not include any duplicate information from previous weeks (i.e. if a tutor has completed basic training a few weeks ago and have been included in previous report they don't need to be included in future reports).
Can anyone help me in setting up the query which the report will be based on???
Any help would be much appreciated.
Regards,
Jonathan
View Replies
ADVERTISEMENT
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
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
Apr 2, 2008
I am trying to use a single record and cell of data generated from a query as criteria in another query but can't figure out how to do it? Is there a way to reference a query field in the criteria in design view of another query?
Details:
I have a table of data for each month with supplier codes in each table, but no date field.
I am trying to build a query that will automatically pick the most recent month of data. I have built a query that uses an iif statement in SQL assigning a number to the most recent month of data and then I am using the max filter in that query to show the single highest number (But this logic misses suppliers some suppliers but does assign a number to the most recent set of data). In this case its 2 for february data. I want to use this digit to filter a seperate criteria that will show me all the supplier codes for the month of february. One problem is that some suppliers are in every month others, come and go throughout the year, so I have to assign this criteria for each month. Therefore I want to use the 2 from the first query and plug that into each criteria section under each month field of the second query.
The other option that I can think of but can't seem to find a fix, is to merge all 12 tables so there are duplicate entries and can have a date or number assigned for each month that the supplier code shows up, then just use the max number. I can't seem to find a way to add all the codes creating duplicates into one field. I used a union query but there are no duplicates with this method thus foiling my plan.
Please help
Thanks for reading
View 1 Replies
View Related
Aug 10, 2005
HI all,
i have a really annoying proble where the solution is probably staring me straight in the face. Basically i want to produce a report where the user is prompted to enter months to be looked at within a certain period and also the years, in my query i am using:
Between [First month] And [Last month]
and
Between [First year] And [to end year]
when i do this the query just asks for the months and doesnt go on to ask for the years...help?! :D
View 1 Replies
View Related
Sep 27, 2005
Hey Guys,
What i want to be able to do, is that when a user clicks on a report, they are asked to enter a month and only the records with that month will be displayed!! What expression can i enter in the citeria of the query, i did try =like"month" but that means creating a query for every month of the year!!!
Thanks in advance guys!!
View 3 Replies
View Related
Nov 21, 2006
Is there a way to have a Criteria set to promt a user to select a item from a dropdown list.
So when the prompt is made to ask for the Item Name it will have a dropdown list that the user can choose from
View 4 Replies
View Related
May 10, 2007
Hi,
I hope someone will be able to help me with this one, I have a simple access query set-up which references to a table containing information about processed orders (From a ficticious company). I am trying to set up a query that will display all order placed within the last week. I have edited the criteria on the date filed to
Between Now() And Now()-"7"
Now this sort of works in that it filters the data, but it doesn't filter it by the day but by the year. i.e. Any order played within the last 7 years rather than the last 7 days. Any help much appreciated.
Sorry for being such a n00b,
Thanks for looking!
cheers
xRes
View 2 Replies
View Related
Aug 24, 2005
I'm developing a report whose query is linked to a resources table containing background info about each of the resources in my department. Currently, the query pulls data for all resources regardless of whether or not they're still employed by my organization. In the resources table there is a field called "end date." If a resource is no longer employed with the organization, we input their date of departure. I only want the report to show info for those employees currently employed with the organization. Hence, I must make note of this in the query. I've tried the following in the criteria box for the end field,however I continue to receive a data type mismatch error. Help would be greatly appreciated. Thanks in advance.
"Is Null"
<> "True"
View 1 Replies
View Related
Aug 25, 2005
hey people, im new to the forum and so far its been v.benficial, iv got a simple database running but can't get a query going; can some one please show me how to do the following on a query:
What do i put in the criteria so that it does not show records with null value?
Thanks
Mos
View 2 Replies
View Related
Sep 7, 2005
Hi all,
In my query i have 5 different fields showing different type of fuel types, these are checkboxes on a form.
I have another field which shows the 'dry time' (this is calculated on two other fields, the time of runout and time of delivery)
What i want to to is have a selection criteria whereby if 'Diesel' (one of the fuel tyeps) dry time is over 06:00 hours, it is displayed.
Im not sure whether this should be in the Diesel field, or the dry time field.
I have tried both using:
'Diesel' > "06:00" and also 'Dry Time' > "06:00"
but niether work...any help would be appreciated. Thanx
View 2 Replies
View Related
Sep 19, 2005
I am trying to get all records that have been delinquent in any part for 45 days. Each record has these categories: G, SC, CL, O. Not all records have data in these categories.
Here's what I have:
I have an expression: Total: Sum([dtmGrec] - [dtmSent])
dtmGrec is the received date for this refund. I am using this to get the 45 days. I have an expression for each category.
The problem I'm coming across is that G may return a result of 100 days and SC returns a result of 4 days. Is there anyway that I can have the result for the SC show 0 or Null?
Thanks
View 3 Replies
View Related
Oct 3, 2005
My forms are based on query that provide little speed while opeing the forms. But as we know when form opens, Access loads all the records into memory which might take time if the reocrds are in 10s of 1000s.
As a step to reduce traffic onto server because of these kind of form loadiing, I set Criteria for each query so that a little amount of records will be fetch up by access and it would be fast.
All fields are bound to the query and displays limited records as desired.
Let us assume that INVOICE table contains 54,000 records. But after seting the criteria on filed InvoiceNo > 50,000 in query, it shows latest 4,000 invoice records (which are greater than invoice no. 50,000). Off course, the form became fast and moreover it reduced traffic on server.
My question is if a user need to see invoice number 48650 which is not availble with the currently loaded invoice form..what should we do ?
Should I use all unbound text boxes on the form and make a save button then write code lines to save record and again write another code lines to read the required record?
Would not be a lengthy process..? Bcz I have plenty of code lines with all the forms and it would be big trouble if I play with it...
View 6 Replies
View Related
Jul 24, 2006
I have a number of filters across the top (6 in total). I want to run any number of these filters and then only print the relevant records that pop-up after I pressed the search button. As you can see now I have searched for records that contain TSA. It shows 3 records from a total of 16.
http://i54.photobucket.com/albums/g115/TomBP/Print.jpg
I had the following in mind but haven't got the knowledge to make it happen:
I need to build a query that picks up the criteria from the form. Then Build a report that is based on this query. So that the query only shows the records that are shown in the subform.
Someone already tried to help me and suggested that I filled in the following in my query:
Forms.[MyForm].[MyField] Or Like Forms.[MyForm].[MyField] Is Null
http://i54.photobucket.com/albums/g115/TomBP/printing.jpg
But my experience of such criteria is'nt all that so I don't know how to typ this so that it matches my database.
My subform = frmSubform
My mainform where the subform is located = frmSearch
I also uploaded my database to yourfilelink.com . If you want to have a look. It's only 200kb.
http://www.yourfilelink.com/get.php?fid=141709
View 3 Replies
View Related
Sep 28, 2006
Hi guys,
I have a newbie question here.
I have a sum in my query, which sums the # of particular units, and want to compare it to a MinimumLevel field. I can get the query to display the Sum and the MinLevel fields, but I can't get them to compare each other and only display the correct results...!
I want to only show records where the SumofQuantity < MinLevel . (The reason I need the sum is because a unit has different lot numbers and have a record for each lot number)
How do I make the correct criteria ??
Hopefully this makes sense. Thanks
View 1 Replies
View Related
Apr 12, 2007
I'd like to only show records in my query that either have an address or an email. If both are empty, it shouldn't show up. I don't know how to go about doing it so it tells if one of the two is at least full. I tried Is Not Null And [Email] is not null, in the criteria for address, but this did not work.
View 1 Replies
View Related
Jun 18, 2007
Hi,
Instead of mention criteria inside query, can I pass criteria through form with some codes? Because I want to use same query for different reports.
thanks for your help
mithani
View 14 Replies
View Related
Jun 22, 2007
I am trying to run a query from two tables.
Table 1 has a Sub Table with a 1 to many relationship.
The PK is the record ID in Table 1.
I want to do a query of all records that have 3 or less records in the sub table?
Show me all records in Table 1 that have 3 or less records in Table Sub.
Thanks!
View 4 Replies
View Related
Sep 9, 2007
I am new to access so I hope someone can point me in the right direction.
I have a database that I need to query by account number and invoice type.
Extracting unique single records is ok but I can not work out how extract multiple records.
If the account number has several records(rows) and mixed invoice types(credit and invoice) is
is there a way to extract all records for an account number if they are just type credit.
account number type
123456 credit
123456 credit
123456 credit
234561invoice
234561 credit
234561 invoice
345612invoice
345612 credit
345612 credit
I would just want to extract account 123456
Any help would be appreciated
View 14 Replies
View Related
Nov 6, 2007
I have a what I think should be a simple enough query, but I'm pulling my hair out trying to get this to work. I have a report based off of a query which prompts the user to either enter a record number manually or simply use the record number for the current record being displayed as the default. My criteria is setup as [Enter Record Number] Or [Forms]![Formname]![RecordNumber]. This works fine if I let it default to the current record being displayed. The problem occurs when I manually enter a different record number than the record currently being displayed, it reports on both record numbers. Any suggestions:confused:
View 7 Replies
View Related
Jan 11, 2008
Hi,
The information I have in a table contains details of Vendor Name, Vendor Number, Amount Spent and then the organisation is given a classification according to what area of business they operate in from a standard code. There are about 350-400 codes in use to classify the type of business activity.
I need to produce a query/report that will sum all of the spend for each classification and display it in a report to enable me to focus on individual groups.
The table is organised
Vendor Number
Vendor Name
Amount
Key (5 digit indexer for the classifcation)
Code (8 digit code for classification)
Classification (Text based description)
All I need within the quqey is for the Amount and Key to be totalled, and Code and Classification to be shown on the report that I'll generate. I attempted to build a function from within Access using SQL Sum with the Amount and Key but it unfortunately didn't work.
Any help would be most appreciated.
Thanks,
View 1 Replies
View Related
Mar 10, 2008
Once again in need of help.
I have a db with articles linked to the articles of a supplier. 1 of our articles can come from more then one supplier. So to make up my order, i choose the supplier and our article number and want to look it up in the db holding the supplier articles.
eg.:
our supplier supl. art.
2001 suplier 1 thx36
2001 supplier 2 895568
2002 suplier 1 TXX895
so the result for supl. 1 with 2001 would be thx36.
When i put this in a query i get an error, to few parameters, expected is 1.
Here is the code:
strSQL = "SELECT tblArtikelKopLev.Artikel, tblArtikelKopLev.Leveranciernummer, tblArtikelKopLev.ArtikelLev, tblArtikelKopLev.OmschrijvingLev FROM tblArtikelKopLev" & _
" WHERE ((tblArtikelKopLev.Artikel = " & strArtikel & ") AND (tblArtikelKopLev.Leveranciernummer = " & strLevnummer & "));"
Anybody any idea?
Thx
View 3 Replies
View Related
Apr 15, 2008
Hi,
I have a query that calls a status field. My status options are 1,2,3,4.
In my query I only want the records that are status 2 & 3 only.
Can someone please tell me how I can do this in the criteria?
If I use "1" it will show only the status 1's but I dont know how to show 1 and 2..
Thanks.
Fen How
View 2 Replies
View Related
Nov 19, 2004
hello all,
I created a form to allow the user to enter in their own criteria with respect to the query I have setup. Here is my situation. I have a form with 3 text boxes "CenterName", "CourseNumber", and "Status" that correspond to the query and the criteria is setup to get information from text boxes on the form. Right now, I can type in either the center name, the course number, or the status. I cannot do center name and course number, or i can't do course number and status, etc... Any advice would be greatly appreciated.
Jared
View 2 Replies
View Related
Nov 29, 2004
How do i offer the option of entering in values into a query? I thinkk it has to do with the
where clause, but i'm not sure! Can someone help me out? I want to be able to enter in
the name of a venue and have only that venue info come up! Thanks in advance!
View 1 Replies
View Related
Jan 12, 2005
I have a user form that has a combo box called Company that is used to limit a report to all or a single company. I am trying to make it where the user can leave it blank (Null) for all companies or Pick a single company. I know there are several ways to take the value of the company (CompanyID) and use it for criteria. What I was trying was to use an expression in the reports query to get my results. This is what I tried to put in the criteria grid in my query.
IIF(IsNull(Forms![frmBillingReports]![Company]), Like "*", Forms![frmBillingReports]![Company])
If I leave the field null on my active form, I get 0 rows back. If I pick a company, I get the right number of rows back for the company the user picks. Now If I put a 1 in the place of the Like "*", I get the right number of rows for company one. Also, If I take the whole expression out and put (Like "*") it brings me back all the rows in my table.
Does anyone have any suggestions?
Thanks!!
View 1 Replies
View Related
