Queries :: Only Show If ID Is Matching In Other Table?

Jun 12, 2015

I have two tables. One with a persons name and ID, DOB, GP etc (TblName) and one with the persons ID and medication they take (TblMedication). I have a query that runs a report. The header is made up of the first table (TblName) and the body of the second table (TblMedication). All works fine but it shows all records from the first table (TblName) regardless of whether they have medication or not so the body shows up blank. I want to run the query with both tables information but only show the persons header if they have medication.

View Replies


ADVERTISEMENT

Queries :: Show All Fields With No Matching Records - One Table

Aug 14, 2013

How to get the following results using 1 table:

Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it

Table A

Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8

Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8

I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.

View 5 Replies View Related

Show All Records That Have More Than One Matching In A Related Table

Dec 16, 2005

Hi,
I've been trying to get this for ages now - both in the design view and in sql:

I have 2 tables - one called DrawingsRegister and a related one called DrawingRevisions. Each drawing has one or more drawing revisions. I want a query that will show each drawing (just once) that has more than one revision:


SELECT tblDrawingsRegister.DrawingNum, tblDrawingsRegister.DrawingName, tblDrawingRevisions.DrawingNum

FROM tblDrawingsRegister INNER JOIN tblDrawingRevisions ON tblDrawingsRegister.DrawingNum = tblDrawingRevisions.DrawingNum

WHERE ((Count([tblDrawingRevisions]![DrawingNum])>"0"));

Thanks for your time,
RCurtin.

View 1 Replies View Related

Queries :: Column From Other Table Displays Default Value Even With No Matching Record?

Apr 7, 2014

I have three tables: First, Second, and Third.The tables Second and Third each have a column with a default value of "X".I'm creating a query that selects fields from First table that JOIN with corresponding fields from Second and Third, so that all records from First are represented and only matching records from Second and Thirdare returned. I also select the "X" fields from Second and Third.

So I run the query: and the "X" column from Second only displays "X" for records that have a match in Second (since I mean for these fields to be quick indicators for if the record also appears in the other tables, this is my ideal result). However, the "X" column from Thirddisplays "X" for all rows, regardless of whether the record has a match in Third or not.

I can get more specific if need be, but I really can't tell why the one is displaying according to matching records but the other is always on. The JOINs are set correctly and the properties for the columns all check out.

View 4 Replies View Related

Forms :: Search Field - Subform Does Not Change To Show Matching Records

May 6, 2013

All. using 2010. I have a form and a subform. my master and child links are set but when I scroll thru my form; my subform does not change to show the matching records. I do have an unbound search field for the same field as the master and child links. When I type in the search field for a record; the subform does change to that particular record. Do you think that has anything to do with it?

View 1 Replies View Related

Queries :: Query Based On Table Matching Another Query?

Jan 13, 2014

Been working on this for a while now and can't get it to work how I'd like after trying a few things.

I have a form ("Production Form") from where users input start and end times for various processes against a particular product. Currently, I have (or had) a query (and linked report) "ProductionDurations" where durations for each of the processes were calculated for different product runs.

I have since tried to adapt this query to include reference values contained in another query ("ProductionDurationsPerBulkLitre", based on a "Products" table) for how long each process should take for each production, by multiplying by the volume processed. Here's what I have at the moment in the "ProductionDurations" query:

Quote:

SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration,
DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration,
DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration,

[Code] ....

Rather than returning what I require, the above bolded part is returning a calculated value for each production against each of the products. What I require is a calculated value for each production against the product that has been selected on the form.

View 14 Replies View Related

Queries To Show Changes In Table

Feb 13, 2006

Hallo!

I'd like to make querie to show all changes made in database (one table) in some period of past time.

Please help me!

Robi

View 3 Replies View Related

Queries :: Show TOP N Records In Table

Jun 15, 2014

I have a table that includes a date/time field. I want to query that table and show all but the TOP N records. Since the number of records will always be changing I cant do it using the BOTTOM N records.

View 2 Replies View Related

Queries :: Show Entries From 2 Tables Not In Third Table

Jun 22, 2013

I have 3 tables;

tblEmployees - Stores employee data
tblProcess - Stores process data
tblTraining - Stores data on which employees have trained on which process

tblEmployees has a 1 to many relationship to tblTraining through empID
tblProcess has a 1 to many relationship to tblTraining through pcsID

When a new Process is added, a query auto populates tblTraining with 1 record for each employee in tblEmployees. Equally when a new Employee is added, a query auto populates tblTraining with 1 record for each Process in tblProcess.

In theory this should ensure that every employee has a training record for every process (and vice versa). However, as many of us know, never assume the user wont find a way to mess things up!

So i want to have a query that can show me Employee and Process without a training record. I have been manipulating a SELECT (SELECT) query to pull this together, but i just keep coming up blank.

I have attached a copy of the relevant tables and query. I have deliberately deleted a training record for empID 6 on pcsID 1 AND empID102 on pcsID 2 (qryCheckTrainingRecordsBalance will show the processes these 2 DO have a training record for).

View 5 Replies View Related

Queries :: Pivot Table To Show Blank Rows

Mar 4, 2014

I have a PivotTable that I am trying to put together that will give me the following:

Types of Payments - Left
Accounts Where Payments came from - Top
Amount of Payments - Data

I want it to look like a spreadsheet where it will show me all the Types of Payments even though I don't have any data in there. Almost like an Excel Spreadsheet but I want it in Access. I was able to get the Columns to show up even though I had some blanks but now I need the Rows to show up.

View 11 Replies View Related

Queries :: Grouping From One Table - Show Result In 2 Columns

Sep 12, 2013

I have one table in my access, and out of that I need to show a queri that will show only following result in 2 columns ID (same as original table)

SCHOOL_YEAR (all the students that are):

1 year and 1 year (repeat) as 1 year
2 year and 1 year (repeat) as 2 year
3 year and 1 year (repeat) as 3 year

Not to show dripouts, na and finished

Original table:
IDNAME
11 year
42 year
53 year
10dropout
11finished
12n/anot categorized
131 year (repeat)
142 year (repeat)
153 year (repeat)

View 3 Replies View Related

Queries :: Combining Queries Without Matching ID Values

Feb 2, 2015

I have two queries:

1. a sum of payments by individuals
2. a sum of charges of individuals.

I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).

View 5 Replies View Related

Queries :: New Record Added To Table But Doesn't Show Up In Query

Aug 4, 2014

I have a database used to track my personal assignments, created about six years ago using Access 2003 on Windows XP. Recently upgraded to Access 2010 on Windows 7. At some point thereafter, I started having the following issue:

When a new record is created, that record gets added to the table, but doesn't show-up in any query, form, or report until after another new record has been added. The most recently added record cannot be located to view or update, except in the table, until after another new record has been added to the table. Queries, forms, & reports now always lag behind by one record.

None of the queries, forms, or reports tested contain filters. I have several multi-user databases that I also support and none of those users have reported having this problem. This is only happening on my personal database.

I've re-created this database once or twice in the past to resolve other issues, but would like to avoid that route this time around, if possible.

View 7 Replies View Related

Queries :: Design View - Show Results In Two Columns From One Table

Jul 28, 2015

Lets say I have the follow Tables:

Code:
Outfits:
ID | Top Color ID | Bottom Color ID
1 2 1
2 3 4

Colors:
ID | Name
1 Red
2 Blue
3 Green
4 Orange

I'd like to have a query in design view to have the following result

Code:
Outfit ID | Top Color Name | Bottom Color Name
1 Blue Red
2 Green Orange

In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name".

View 4 Replies View Related

Queries :: Sum Of Non Matching Results

Jun 16, 2013

I have a table with sales in (TBL_Sales) as well as another (TBL_Key_Customers) which lists information about specific customers, in particular if they are part of a group e.g I would categorise Dave's Cars, Dave's Bikes and Dave's Coaches as being part of the Dave group.I would like to query the TBL_Sales to see how many sales were made to the Dave group but then also what else was sold. e.g if Factory 1 sold 100 items of which 60 went to Dave's group then the remaining 40 would be shown as "Other".

View 4 Replies View Related

Queries :: Count Number Of Times Each Of Responses Show From Lookup Table

May 9, 2014

I have 2 tables...a lookup table with possible responses (i.e. yes, no, don't know) and the second with 8 fields that contain the values selected from the first table. Tables are related one-to-many. I need to count the number of times each of the responses shows from the lookup table shows in each of the fields in the main table. Have tried to query with the lookup field set to "group by" and each field set to "count".

View 2 Replies View Related

Queries :: Matching Values Without Duplicates

Oct 23, 2013

I'm looking for a way to grab values from one table that match a criteria, and append them/update them to another, but only grab one value once. Example:

Part ID ---- Profile Profile ID ---- Locations
Part1 - Profile 1 Profile 1 ---- Location 1
Part2 - Profile 1 Profile 1 ---- Location 2
Part3 - Profile 1 Profile 1 ---- Location 3

I have a database of parts that fit in certain profile sizes. Each profile size has multiple open locations. I need a query to grab a location, assign it to a part, grab a new location, assign it to the next part, etc, without assigning duplicate locations to different parts.

Right now, I'm doing this by using the First/Last aggregate functions, marking them as used, and looping it again and again until all have unique locations. Is there a better way to do this?

View 8 Replies View Related

Queries :: Matching Two Tables With Count

Jan 20, 2014

trying to create a query in which I can select a count across two tables with matching postcodes.

Table 1 : Address database with column called 'STRIPCODE' (e.g. ME8, ME9, ME10)
Table 2 : Area Code table; each column has a list of area codes to match to the STRIPCODE column. (e.g. MEDWAY column will have all of the relevant area codes)

I would pretty much want a count of each match as below :

STRIPCODE
MEDWAY 650
BLACKPOOL 563
HULL 352

Have a few hundred areas to match and tired of going through these manually and entering counts!

View 3 Replies View Related

Queries :: Query Data Not Matching?

Apr 19, 2013

I have a table that holds course information for students. I have a parameter query linked to this prompting the user to enter a course code thus displaying all students who have that course. However, I would like to do the opposite i.e prompt the user to enter a course code and then the result to be a list of students who do not have that course.

View 8 Replies View Related

Queries :: Date Range Matching

Jun 4, 2015

I have two tables. One is about 160 thousand records, which is a part number, a contract price and a day the price took effect. The items appear several times. Prices go up and down over the course of the data, and items are added and dropped.The second table is a list of dated sales and quotes of those items over the past 14 months.(About 10 thousand lines)

I need to match the items with the contract price that existed on the day the quote or the sale was created, so as to demonstrate we were always at or below the contract price.

View 1 Replies View Related

Find Matching Data Between 2 Queries

Aug 23, 2012

I am managing a tool room and one of my tasks is to update a chit board where people sign out their tools. I have managed to make a query to find New Employees and one to find Terminated Employees. I have also made a query to find the matching employees between these two queries.

My problem is that we employ both contractors and direct employees. When a contractor is hired on directly, their name shows up in both the New list and the Terminated list.What I want to do is have a separate query that shows contractors who went direct and not have these employees show up in the other 2 queries.

Both queries are unmatched query that compare the "Current Chit Board" table to the "Weekly Roster Check" table. The first query called "New Employees" displays the data from the "Weekly Roster Check" table where Employee Number from "Current Chit Board" Is Null. The second query called "Terminated Employees" is the exact oposite. The third query I have is called "Contract to Direct" compares the "New Employees" query to the "Terminated Employees" Query with a "fields from both tables are equal" join type.The problem is when I try to do an unmatched between either the New or Terminated Emplyees queries and the Contract to Direct query, I get a Circular Reference warning and it doesn't execute.

View 3 Replies View Related

Queries :: Design A Query To Show Only Empty Field As TEXT Fields In Table

Mar 2, 2014

I am just querying a single table, no relationship involved with another table. As you can see form the attached jpeg, the ZIP field in some cases is empty. I would run a search using Is NULL but the field is NOT numerical. It's a long story but I had to make this field a TEXT field. Basically, what statement do I have to insert in the criteria field to just pull up the EMPTY ZIP fields?

View 2 Replies View Related

Queries :: Count Matching Postcode Between Two Tables

Sep 1, 2014

I have two tables, one being an 'Address' file and the other an 'Area' file which is a list columns ranging from 1-400 with groups of district level postcodes. For Example; column 1 will have postcodes such as BN1, BN2, BN3, BN4, BN5 etc. Each column has a separate group of postcodes.

Now, my 'Address' file also has a column within this labelled 'District' in order for me to create a straight join (between column number and District) in a query to count each group individually, but of course, do to this 400 times becomes quite tedious! (to say the least)

I would require the outcome to be as follows :

Area file - Count
1 - 469
2 - 201
3 - 0
4 - 3598
5 - 268
. - 101
. - 8936
. - 563
399 - 287
400 - 41

View 5 Replies View Related

Queries :: Matching Users And Status In Query

Jun 19, 2015

I have a database where there are 3 tables. table "d_cases", table "d_user" and table "d_status"

The table "d_cases" has the users and statuses only with a number. Peter has ID 2, John ID 1. There is status ID 1 that is New, ID 2 Started and so on.

How can I run a query that will show me the names and status instead of the numbers?

I have attached the database (2003 format) ....

View 2 Replies View Related

Queries :: Matching Year Field In 1 Query

Feb 5, 2015

Using Access 07

This is my query

SELECT QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation, Sum(QryReserveTotals.SumOfAddtlAllocationAmt) AS SumOfSumOfAddtlAllocationAmt
FROM QrySiteAlloc LEFT JOIN QryReserveTotals ON QrySiteAlloc.Site = QryReserveTotals.Site
GROUP BY QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation;

In the QryReserveTotals is a datereceived field.

I need the year of the date received field to match the QrySiteAlloc.AllocYr that will be selected by a parameter..

what would be the criteria to achieve this?

View 3 Replies View Related

Queries :: Matching Fields From Multiple Columns

Jun 26, 2013

I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.

OTHCPT2OTHCPT3OTHCPT4OTHCPT5OTHCPT6OTHCPT7
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 #NULL!#NULL!
2318095978C176788307J0131 J2270

For instance if 23180 and J2270 are in the same record I want to identify them for all of the records.

View 7 Replies View Related







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