Return All Records Where An Id Field Occurs More Than 3 Times
Jul 12, 2006
I just can't get my head round this one and can't even think of a keyword phrase to search on!
SELECT CompID, CaseRef, ServicesID, Summary
FROM qryAllStats;
In my query I need to return all records where there are more than 3 instances of field 'ServicesID' .
For instance in the recordset ServicesID 47 occurs 5 times while ServicesID 36 occurs 4 times and all other ServicesIDs occur 3 times or fewer.
I would need to return all of the records with ID 47 and 36. Is this do-able?
View Replies
ADVERTISEMENT
Nov 1, 2014
I am trying to write an access SQL query that returns all records shared between table1 and table2 LESS THAN 3 TIMES.
application has 4 tables: Patients, AnnualPhysical, Doctors, and PatientPhysicalDoctors.
each patient has an annual physical (once a year), and chooses one doctor per annual physical.
each doctor can only perform three or less annual physicals per year.
It is a small application with less than 1000 records per table.
the query will allow the patient to choose an annual physical doctor once a year from available doctors.
Patients is one-to-many to AnnualPhysical;
AnnualPhysical is one-to-many to PatientPhysicalDoctors;
Doctors is one-to-many to PatientPhysicalDoctors.
query for all records NOT shared between Doctors and PatientPhysicalDoctors is:
SELECT *
FROM Doctors
WHERE NOT EXISTS( SELECT * FROM PatientPhysicalDoctors
WHERE Doctors.DoctorID = PatientPhysicalDoctors.DoctorIDFK );
View 3 Replies
View Related
Sep 23, 2005
Trying to figure this out, but I’m not getting anywhere.
I have 2 tables one is just a list of options that can be used in a drop down box in the other table.
What I am trying to do is create a query that will count how many times the words from the first have been used in the second query. I have been able to get it to count how many times the word has been used but I also need it to return a 0 if the word has not been used.
In my query I used both tables to try to do this but it only returns the ones that have been used.
I will attach the DB if someone is willing to take a quick look at it to see if they can get it to work.
Any help will be greatly appreciated.
Thanks in advance
Jon
View 2 Replies
View Related
Sep 5, 2013
I have a field in Access 2010 called Section Number which generally holds two characters. The characters can be numbers, letters, or a combination. Examples are:
01
X1
KA
40
Sometimes this field holds three characters, such as:
01a
02b
I want to write a query that returns only the Section Numbers that contain two characters. The instances where the field holds three characters are too numerous and changeable to use a parameter that says, for example: not "01a" and not "02b"
Is there a way to write a parameter that means "not Section Numbers of three characters" or "not Section Numbers of more than 2 characters"?
View 2 Replies
View Related
Apr 8, 2013
I am trying to create a query that returns records whether a field has data or not...
There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.
I would like to do this in the criteria box in the query.
The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...
View 2 Replies
View Related
Jul 23, 2013
I am trying to create a query pulling from several tables. I will use the example below to illustrate what I'd like to do.
I have a field called 'Acc_Num', one called 'Stat_Code', and a third called 'Cat_Code'.
Each 'Acc_Num' can have multiple records because there are multiple Stat_Code and Cat_Code values.
What I want to do is isolate just the Acc_Num records where Stat_Code = 1 and Cat_Code equals A1.
I also want to isolate the Acc_num records where the Stat_code = 1 and where Cat_code does not exist.
View 7 Replies
View Related
Apr 6, 2014
I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.
tblPersons
PersonName PersonDate PersonShift
Jason 4/10/14 FIRST
Jason 4/13/14 FIRST
Jason 4/13/14 SECOND
So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.
The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?
If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?
I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.
View 2 Replies
View Related
Aug 3, 2013
Say i have a table which contains payment information for various people - similar to this
Person Pay Date Pay Amount
User1 01/01/13 £10
User2 01/01/13 £10
User1 10/01/13 £0.00
User1 15/01/13 £0.00
I need to create a query which shows me only results for each person who has paid £0.00 more then once...
View 4 Replies
View Related
Jan 23, 2008
ok so im not sure how to explain this but ill give it my best shot.
I have an excel sheet that has a list of order info. heres a sample of what it looks like:
70144:37.0Starting order split <34010993;01>gregory_pm34010993
70245:16.0Script complete for <34010993;0106>Rx <24073318>gregory_pm34010993
70103:37.0Starting order split <34010995;01>knox_br34010995
70204:26.0Script complete for <34010995;0101>Rx <24239630>knox_br34010995
70126:49.0Starting order split <34011015;01>flander_ar34011015
70232:09.0Script complete for <34011015;0101>Rx <24008174>flander_ar34011015
70134:01.0Starting order split <34011015;01>flander_ar34011015
70127:08.0Starting order split <34011061;01>flander_ar34011061
70227:26.0Script complete for <34011061;0105>Rx <24240139>flander_ar34011061
70227:55.0Script complete for <34011061;0103>Rx <24240083>flander_ar34011061
70152:00.0Starting order split <34011173;01>parker_tp34011173
70252:46.0Script complete for <34011173;0101>Rx <24071140>parker_tp34011173
70103:35.0Starting order split <34011369;01>sexton_pa34011369
70204:00.0Script complete for <34011369;0101>Rx <24240569>sexton_pa34011369
70149:02.0Starting order split <34011668;01>knox_br34011668
70250:59.0Script complete for <34011668;0104>Rx <21441348>knox_br34011668
70134:20.0Starting order split <34011764;01>hicks_jo34011764
70234:44.0Script complete for <34011764;0102>Rx <22787965>hicks_jo34011764
70235:06.0Script complete for <34011764;0101>Rx <22787933>hicks_jo34011764
70236:11.0Script complete for <34011764;0103>Rx <22788283>hicks_jo34011764
70156:45.0Starting order split <34011855;01>knox_br34011855
______^this line is the time
ok so here is my problem. I need two records, one for the start of the order(the earilest time) and one for the closing of the order(the latest time). I also need the time inbetween the two.
Thanks!
View 3 Replies
View Related
Feb 1, 2015
I have a table (Imported from Excel) with In and Out records.
Here are the problems I have:Each record has either In or Out data. I have an identifier to know which ones are In and which ones are Out
It might have some misses, either no In or no Out record for the specific work time. A worker can get in at the evening and get out at the morning of the next day. Might have more then one entrance/exit on the same date.
I want to have the record sorted so I'll have an In and Out on the same row (record) including holes for the missing ones.
View 9 Replies
View Related
Apr 4, 2012
I have one table like:
ID Type No
-----------------
1 FL 2
2 DL 3
I need the following table to be created using first table , each record should be duplicated according to value in No field:
ID Type No
-----------------
1 FL 2
1 FL 2
2 DL 3
2 DL 3
2 DL 3
View 2 Replies
View Related
Oct 3, 2006
I hope someone can help me on my problem below:-
A user wanted to key in the data in a single form where she can select where
a particular procedure is located. The procedure could be duplicated and
placed in a few departments.
My tables are as follows:-
tbl_Proc_Dept:-
ProcNo - Text (Primary)
TrgConducted - Yes/No
DeptAbbv - Text (Primary)
tbl_Dept:-
DeptAbbv:- Text (Primary)
DeptDetails:- Text
I created a form where i have the following fields in the form:-
1. ProcedureNo - Text
2. TrgConducted - CheckBox
3. MainDept- ComboBox (DeptAbbv data field)
4. Finance – CheckBox (DeptAbbv data field?)
5. Admin – CheckBox (DeptAbbv data field?)
6. Purchasing – CheckBox (DeptAbbv data field?)
7. Facilities – CheckBox (DeptAbbv data field?)
8. MIS – CheckBox (DeptAbbv data field?)
How can I add multiple deptabbv field in a single form?
Pls help.
Thanks.
View 1 Replies
View Related
Aug 29, 2005
I have Microsoft Access 2002 sp3
I have created a query that can used for tracking how many times a file is looked at or reviewed. My question is if I have a Field labled "Activity Date", can another field be created to add up how many time the date is changed in the "Activity Date" field. I am new to Access and have been learning on my own so any help would be greatly appreciated.
thanks in advance
jdubs
View 2 Replies
View Related
Aug 29, 2005
I have Microsoft Access 2002 sp3
I have created a query that can used for tracking how many times a file is looked at or reviewed. My question is if I have a Field labled "Activity Date", can another field be created to add up how many time the date is changed in the "Activity Date" field. I am new to Access and have been learning on my own so any help would be greatly appreciated.
thanks in advance
jdubs
View 1 Replies
View Related
Dec 30, 2014
Preamble edit: I'm running Access 2003.
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
View 3 Replies
View Related
Apr 2, 2013
I'm compiling a monthly report based on data from my table "JobSheet"
I want to count how many times last month a specific field was entered, it's a drop down list which is formed from it's own table "Problem"
Is there a simple way of doing this?
View 3 Replies
View Related
Jun 8, 2015
I want to use to count how many times a record is displayed in the period and lesson field, in another table, so that this data can be used to show how many people are attending a class. e.g how many times people are attending art 1, by counting how many times art one appeared in the first table.
View 1 Replies
View Related
Jul 18, 2013
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
View 4 Replies
View Related
Jun 2, 2013
I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.
Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.
I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.
I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?
View 3 Replies
View Related
Sep 23, 2013
How to count the number of times that the data appears in a certain field (which is [Ema!l]). I have a list of ema!l addresses and I want to find out whether that ema!l address has appeared once, twice or more. I want to add this as a field in a query. I don't want to delete it or anything because it's perfectly fine for the ema!l address to appear more than once, I just want to be able to identify when the ema!l address has already appeared.
If I was using Excel I would use a Countif function to count how many times the data in the specified cell appears in the whole column, and that would give me the number. I'm not use to the language of Access so I can't figure out how to achieve this.
I tried adding a Total row to the grid in the query and then changing the total to 'count' but this just returned 1 for every row.
View 3 Replies
View Related
Apr 7, 2015
I have a question related with a report i`m using to print labels. I am not familiar with Access and this is quite a challenge to do it alone.
In the attached file there is a report called "MICRA", when started it asks that you want to select (default is 1 and special select in this case is 11) and next it asks "SPS", the value entered there is printed in the bottom right corner of the report.
My question is is it possible if in the SPS field is entered a special value (for example "MASS") to print 30 labels of each selected label with text in this fiels = "val.1"; 15 with text in the field = "val.2"; 10 with "val.3" and 10 with "val.4" and after that to print next label with same rules...
And if not entered "MASS" to print just 4 copies of every label.
View 6 Replies
View Related
Jun 10, 2013
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View 14 Replies
View Related
Aug 6, 2005
I have a database called LettersDatabase this databse holds all the letters that have been made including the path to the doc. I use SSN to ID the letters to customers on the Contacts Database.
Contacts database also uses the SSN to id the contacts
I have a form that creates new letters for customers in this form I have listbox that queries the LettersDatabase for all matching records based on the forms contact SSN to see how many letters have been made for that customer.
The problem is that my listbox only shows the first record matching that SSN but there are more records in that LettersDatabase with the same SSN that I need to have diplayed on the listbox as well.
I may be writing the query incorrectly.
Here what I have for the query on the listbox
Like[Forms]![LetterMaker]![txtSSN]
I try adding (&"*") to the end of the query but that does not help.
If anyone out there has the solution to this problem it would be greatly appreciated
Thank you
View 3 Replies
View Related
Jan 31, 2006
Hi All,
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
Thanks in advance:D
Cheers,
Karv
View 2 Replies
View Related
Oct 1, 2007
Hi.
i.e
I have a query with the following sort of information:-
MonthAmount
08/2007£100
09/2007£200
10/2007 £55
11/2007£60
As today being the 1st October, I would like the query to return the values for Oct and Nov.
The same values would be returned on the 31st Oct, but then the following day, only the Nov value would be returned...so, basically, it only returns the values of the current month and following months..
I am assuming I put something in the criteria of the query under the month? I have tried a number of things, with none working!!!
Thanks for reading....
Frank.
View 1 Replies
View Related
Sep 20, 2013
I would like to do a loop but never done one before, basically i want to copy the current record by the number of times specified in a quantity field
So if the quantity field in the record says 5 then copy that record 5 times (I have managed to create the copy and paste code but dont know how to make it do it 5 times
Code:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
View 2 Replies
View Related
