Queries :: Return All Records Shared Between Two Tables LESS THAN 3 TIMES

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 Replies


ADVERTISEMENT

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 3 Replies View Related

Trying To Figure How To Count How Many Times A Word Has Been Used And Return 0 If Not

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

Queries That Return No Records

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

Write A Query That Will Return Records From Multiple Tables

Dec 19, 2012

I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.

The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.

View 14 Replies View Related

Queries :: Return All Records With Last / Max Date

Apr 10, 2013

My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:

Field A Field B Field C
123456 AAAA 1/8/13
123456 BBBBI 1/8/13
123456 CCCC 1/8/13
123456 DDDD 1/8/13
123456 EEEEEE 3/10/13
123456 FFFFFF 3/10/13
123456 GGGG 3/10/13
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13

The desired results would be to return all records with the last/max date, so yield:

123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13

I have tried the max & last functions, yet no success.

View 1 Replies View Related

Queries :: Return Values From Two Tables?

Feb 22, 2014

I want to return the price from my transactions table for the value '1' in the ID field in this transactions table.

That would be my first field in the query. The next one would be to do a sum of the sellprice field in my 'inventory' table where the buyingID matches a value of '1'.

how to accomplish this? So far, I'm not getting the query to return anything on the relationship.

View 4 Replies View Related

Queries :: Query To Return Records For A Given Year

Feb 3, 2014

I'm trying to create a query to show me records for a given year. The issue I'm having is that each record has (4) dates fields and each record can contain null values.Is it possible to do this in a query with data like the example below?

Field 1 ID (1), Field 2 Date (12/22/2012), Field 3 Date (2/06/2013), Field 3 Date (Null), Field 4 Date (Null)

In this example I would want 2013 data but would need to be able to search any year. I could also have dates in all (4) fields

View 3 Replies View Related

Queries :: Return Most Recently Created Records?

Mar 3, 2015

query to return the most recently created records in a composite key field, and only the most recent ones.

there is a screenshot of the composite key field attached

I want to get te records with the highest InvoiceID, there can be multiple records with the same InvoiceID and I want to get all of them.

Using the screenshot as an example I need to query to return the last two records as they both have the Same, highest InvoiceID.

View 6 Replies View Related

Queries :: Return Records With No Data In A Cell?

Apr 4, 2015

I've got a database of club members with names, addresses, contact info etc.

I need to send letters by post to those members that I don't have email addresses for. How to create a query to return a list of people whom I don't have email addresses for.

I know how to mail merge the info with the letter I've written in word, how to group people who's email address cell is empty.

View 1 Replies View Related

Queries :: Return Records With Partial Match

Feb 14, 2014

I understand that a query can accept wildcards in the search criteria but this is different.

I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".

I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.

In other words, the record entries themselves need to be treated like wildcard entries.

View 7 Replies View Related

Queries :: Return Result When No Records Are Found?

May 2, 2013

Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?

Otherwise is there a way to make a master query ignore sub-queries if they return no records?

Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.

These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.

Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.

The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).

Here's an example of my sql statement for the modal queries.

Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate

HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))

ORDER BY Count([Toolbox Talks].TrendID) DESC;

- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.

View 6 Replies View Related

Queries :: Return Records With Both Empty And Populated Field?

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

Queries :: Return Records If One Field Is True And Another False

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

Queries :: Modifying A Query To Return Only Instances Where There Is More Than 3 Records

Sep 26, 2014

I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.

Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.

I am thinking along the lines of DCount? but how it would be phrased in the query?

View 14 Replies View Related

Queries :: Table With Duplicate Records - Return Distinct Data For Each Client

Jan 17, 2014

I have a table with duplicate records (which is ok) and I want to return distinct data for each client.

It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.

I.E

SELECT DISTINCT tblClient.ClientNo, tblClient.Name
FROM tblClient

Works fine with only the Distinct records for each client returned

However

SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue
FROM tblClient

Now returns Duplicates!

Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?

View 5 Replies View Related

Queries :: Return Records Between Dates Based On 2 Date Fields In A Table

Apr 24, 2013

I have a table which includes a start date field and completion date field for housebuilding.

I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.

It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.

View 7 Replies View Related

Queries :: Query That Will Return Records From A Table That Have Related Records In Another Table

Mar 4, 2015

I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.

View 3 Replies View Related

How To Link 4 Databases To Shared Tables?

May 19, 2006

I have 4 databases that share 3 tables.

Each database is used for different purposes and other tables which do not relate to the other databases.

My question is: how do I set up the back ends so each database can retrieve and enter data into the 3 shared tables without conflicts??

I know how to split each of the databases to front and back ends but how would I link them all to the 3 tables they share? Should I create one back end with only the 3 shared tables and link the separate back ends for each database???

Any help much appreciated!

View 4 Replies View Related

Queries :: Return List Of Records From Original List

Apr 23, 2013

I'm looking to move an excel sheet to access because the row counts are too much.The main thing it does is compare the supplied data against a list I hold in the sheet.There are not duplicate records, however..Some data is a direct lookup for a full match, but much of it is a count to see how many records contain a certain string.

I have 500 keywords which have a countif function in using wildcards.I need to create a query/report which will return a list of records from the original list which contains each keyword featured and how many times it features.I was going to do it in PHPmysql but the time it took to parse a million records for every keyword made it pointless.

eg:
keywords:
look
billy
magic

list:
"have a look and see"
"spanish dave"
"who is billy brag"
"looky looky I go hooky"
"who's the man from argentina"
"could it be magic now"

my spreadsheet would return a 1 next to ""billy" and "magic" and would put a 2 next to "look".

the sheet has the keyword in each row and next to the column:
=COUNTIF(list,CONCATENATE("*@",B13))
where "list" is the external data.

View 3 Replies View Related

Sorting Records By Times

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

Tables :: Recording Log In And Out Times By User

Mar 24, 2015

how I record user log in/out times in a table with Access 2013.

View 2 Replies View Related

General :: Identifying In And Out Work Times Records

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

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Code:
Between [StartDate:] And [EndDate:]

And

Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related

Queries :: Sum Between Two Times

Nov 22, 2013

I work for a chemical company, over the course of the day there are 2 shifts, one from 07:00 - 19:00 and the other from 19:00 - 07:00. I want to measure the amount of time a piece of equipment has been offline for, and Sum these times for each shift, these pieces of equipment can go offline multiple times per day which is why I need the sum function. The fields I currently have are:

[Date1] : The date at which it went offline
[Time] : The time at which the piece of equipment went offline
[Duration] : The length of time it was offline for

For each day I wish to sum the offline time in two fields called:

[Shift 1]: The previous nights shift (between 7pm the day before and 7am that day).
[Shift 2]: The days shift (between 7am and 7pm that day).

Where to begin but the logic should be something like:

[Shift 1] = Sum of [Duration] For(([Date1]-1 and [Time] between 19:00 and 23:59) And ([Date1] and Time between 00:00 and 07:00))

[Shift 2] = Sum of [Duration] For([Date1] and [Time] between 07:00 and 19:00)

I currently have two tables storing all this data, one containing [Date1], and another containing the breakdown information such as [Duration], [Time] etc. They are linked by the [Date1] relationship. The reason for the first table only having the [Date1] field is because it is a Main Form which contains various sub-forms.

View 3 Replies View Related

Queries :: Between Two Times Within IF

Mar 29, 2014

I am trying to build a query that depending on the value stored in a combo box called SHIFT, either lists all the entries that have been made by our dayshift (07:00-19:00) or our night shift (19:00-07:00)

Each of these components work if I seperate them out

IIf([forms]![FRMFRame74]![SHIFT]=1,Between #07:00:00# And #19:00:00#,Between #19:00:01# And #23:59:59# And Between #00:00:00# And #06:59:59#)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved