Queries :: Getting Rid Of Records In A Query
Oct 7, 2013
I have a report that is producing duplicates.
In my query I determine one of the fields using nested conditional statements
e.g. iif(component =1 and fred = "z", component, iff(component = 2 and fred ="x", "stuff"), "xxxxx")
(this may not be syntactically correct but bottom line I want to stick xxxx in field if conditions are not met and if that is the case I don't a) want that record in the query or b) I don't want to print the record in the report.
I have tried using the criteria in the query (<> "xxxx") but still get the record. Not looked at seeing if I can remove it from the report somehow.
View Replies
ADVERTISEMENT
Oct 1, 2014
I have a query with three sub queries, all returning a number from different tables. But when any one of the sub queries returns a blank, the entire main query is blank.
How do I stop this from happening? I have tried NZ() on the sub queries and on the main query, but the blank still happens.
View 1 Replies
View Related
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
Aug 14, 2015
I'm running into an issue where I'm trying to tie several queries together into a list one running total. I have six queries that pull data from the same table, but that meet specific criteria. What I was trying to accomplish was to have a 7th query count the records in each of the six queries, and return the results as a different value for each. Here's an example:
Queries:
qry_1A
qry_1B
qry_2A
qry_2B
qry_3A
qry_3B
Final product:
qry_totals:
1A | 1B | 2A | 2B | 3A | 3B |
20 | 15 | 33 | 19 | 12 | 6 |
What I tried:
Field: 1ATotal: Count([qry_1A].[valueName])
Total: Expression
And I did this for each field that I wanted the query to return, so:
Field: 1BTotal: Count([qry_1B].[valueName])
Field: 2ATotal: Count([qry_2A].[valueName])
Field: 1BTotal: Count([qry_2B].[valueName])
Field: 3ATotal: Count([qry_3A].[valueName])
Field: 3BTotal: Count([qry_3B].[valueName])
The problem is that I don't get what I expected - the query appears to be totaling all the records counted and applying that value to all the fields, so I get this:
1A | 1B | 2A | 2B | 3A | 3B |
105|105 |105 |105 |105 |105 |
View 2 Replies
View Related
Aug 17, 2015
I have made a database to catalogue my coin collection, I have a table with various fields for logging data about each coin.I want to try and make a query that can easily filter the coins that I have the same year so I can easily see which sets of coins I have in each year, without having to type in each year.
So basically I want the query to detect that I have a 3 pence, 2 pence and shilling all dated 1887 and crown and half crown dated 1945 etc.
View 1 Replies
View Related
May 29, 2015
I have a table 45 fields. There is a field ID. I would like to get the records where fields contains "No". I would not like to create 45 queries for each field check. how can i generate a table which shows ID, Field Name that contains value "No".
View 4 Replies
View Related
Aug 22, 2013
why this query pulls every record twice and what I can do to fix it?I did it in design mode because I'm an amateur, but here is the SQL.
SELECT [Patient Data Table].[First Name], [Patient Data Table].[Last Name], [Patient Data Table].[C MRI Facility], [Patient Data Table].[C MRI Sched Date], [Patient Data Table].[C MRI Scan Done]
FROM [Patient Data Table], [Date Range]
WHERE ((([Patient Data Table].[C MRI Scan Done])=Yes) AND ((Month([C MRI Sched Date]))=[Date Range]![From Month]) AND ((Year([C MRI Sched Date]))=[Date Range]![From Year]));
View 6 Replies
View Related
May 10, 2013
I have constructed a neat database for randomly quizzing myself on French translations. However I need a simple way of counting the records in an underlying query "vocabularyQ" inside a sub routine. I have tried all sorts of statements the most recent being
SELECT Count(VocabularyQ.ID) AS CountOfID FROM VocabularyQ
View 5 Replies
View Related
Jul 30, 2013
Currently I have a query, QRY_Test which contains a load of records under the fields "Gift_ID" and "Person_ID". I have another query, QRY_PersonList in which I would like to show all of the "Person_ID"s except the ones returned by QRY_Test.
View 3 Replies
View Related
Sep 22, 2014
in my query i have multiple tables, one table just has one field called product thats joined to another table the other table displays a list of all products, if the product is in the other table.i want a "X" to display in that field both columns are text the IF im using is,
IIf([Product] Is Null,"","X")
but when i run this, it displays no records what so ever, if i take out that iff, it displays all records i want, but that field is the product number when i need an X
View 4 Replies
View Related
Aug 15, 2013
How can I modify the below code so that it only adds new records to AttributesTBL from ProductInformation-Consumer?
INSERT INTO AttributesTBL ( Material )
SELECT [ProductInformation-Consumer].Material
FROM AttributesTBL INNER JOIN [ProductInformation-Consumer] ON AttributesTBL.Material = [ProductInformation-Consumer].Material;
View 2 Replies
View Related
Jul 24, 2013
I have a query which displays sales and cash deposits for the day. I want a sum of all cash deposits.
So it looks like this..
Date________________Amount_______________SumOfAmou nt____________Exp1
17/7/2013___________$55___________________$55__________ __________$55
17/7/2013___________$22___________________$22__________ __________$22
I tried doing it by two methods.. neither of which are working.
In the Amount field, I put it as a Total: Sum. I also tried creating a new field which is an expression which sums up the Amount field. Neither are summing it and just displays the value.
View 1 Replies
View Related
May 3, 2014
I'm having a problem get a query to select all of the records it should be.
When I filter the source table (200_STANDARDIZED NRGL) to show the data I want to see (PC2 = 6000; GAAP = 02; CGL = 0950, 2735, 2736, 3500 and 3501; STD VENDOR NAME = blanks), I get 33 records.
NOTE: Had to take the PC2 records that were not '6000' out of the dB I've attached in order to be able to send dB bu the PC2 filter is needed in the complete dB.
When I create a select query to the do the same thing, I get either:3 records (when I set STD VEND NAME to Like '') or 30 records (when I set STD VEND NAME to NOT Like '*' ) I've attached the dB - Query 1 is the subject of bullet 1 above and Query 2 is the subject of bullet 2 above.
BTW, the three records that show up in Query 1 are the same records missing from Query 2.
View 3 Replies
View Related
Jun 5, 2013
Only one table : Customer ID,Customer name , subscription period, subscription start date and subscription end date.
I need values where subscription start date is between date1 and date2 and subscription end date is between date 1 and date2.
Detail explanation:
customer1 subscribed from 2-feb 2012 to 2-feb 2013
customer2 subscribed from 5-aug-2012 to 5-aug-2013
customer3 subscribed from 1-Jan-2013 to 31-Dec-2013
when the user enters date 1 as 1-jan-2013 and date 2 as 30-Jun-2013
Even though the subscription start date is not within the range entered by the user.I need to get all three customers because the subscription enddate falls within the range.And customer 3 is also required as subscription start date is within the range.
View 2 Replies
View Related
Mar 4, 2014
I'm looking for a way of ranking records within a query. Access doesn't have a Rank formula like in excel ...
View 5 Replies
View Related
Aug 10, 2005
Hi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check
Y ACD 2 x x boxes)
Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query
Y ACD 2 8/8/05 9/8/05 10/8/05 performs calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. So for month 1 & 2 I get dates for Lot X and for Lot Y I get dates for month 1 & 3. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
Thanks,
CB
View 1 Replies
View Related
Feb 19, 2015
I have a few queries which are used to create reminder email on training which is due for renewal.Some training required reminder 6 months before 2 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-18,1) And DateSerial(Year(Date()),Month(Date())-17,0)
Some training required reminder 3 months before 1 year expiry. I use this in the criteria for the training date within query:
Between DateSerial(Year(Date()),Month(Date())-9,1) And DateSerial(Year(Date()),Month(Date())-8,0)
My problem is with training requiring reminder 6 months before 3 year expiry. Using this criteria:
Between DateSerial(Year(Date()),Month(Date())-30,1) And DateSerial(Year(Date()),Month(Date())-29,0)
Doesn't show any results (although there is training which was done 30months ago, expiring in 6 months time).
Changing the -30 (months) and -29 (months) in above down to -22 & -21 shows records as expected, but anything below -22/-21 doesn't show any records.
View 3 Replies
View Related
Oct 25, 2014
I have a Unmatched query that displays what I need but I want to clean up my database and remove them but its not letting me. See sql below.
Code:
DELETE Warranty
FROM Warranty LEFT JOIN Serials_Repair ON Warranty.[Serial] = Serials_Repair.[Serials_Repair]
WHERE (((Serials_Repair.Serials_Repair) Is Null));
I changed "Delete" it was "Select" thinking that would work but it does not.
View 3 Replies
View Related
Dec 31, 2013
I have a query that selects records based on certain flags in each record. I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form. Is there any way to automate this process?
View 14 Replies
View Related
Mar 4, 2015
i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.
View 2 Replies
View Related
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
May 28, 2014
If I have a table called "Calls" with an autonumber and another field with names for stores like this:
1 Hobby Mart
2 Peter's Store
3 Hobby lobby
4 Hobby Mart
5 Peter's Store
How can I build a query to count the number of different store names?
For example, in this case I need the query to return the number 3.
View 7 Replies
View Related
Jun 24, 2013
I have two tables: Table #1) [Request Table] Customers request our company to stock a certain Part Number with a minimum value. Ajax - P/N "NP4i" - Min "30".
Table #2) [Sales History] All sales purchased and the "Qty-Total" purchased. For simplicity Fields are limited.I created a CrossTab Query that displays from [Request Table] P/N's requested/ min values (Row Headings) then showing P/N's purchased [Sales History] Value="Qty-Total" Sum and
Column=Format([Invoice Date],"mmm/yyyy") group by, showing last twelve months.
Ex: Customer name, Part Number, Minimum, May/2013, Apr/2013, Mar/2013 and so on.
AJAX, NP4i, 30 4 21 5 and so on.
The Crosstab query list only requested Part Numbers that have at least One sale wihin the last twelve months.If there are NO sales, the crosstab query will NOT show this Part Number.My boss wants to see the requested Part Numbers that were never purchased along with the ones purchased.He wants to address the NO sales, yet they requested we stock that particular Part Number for them.
Is there any criteria I can use to force the Part Number field on the [Request Table] side to populate even though there were NO sales within the last twelve months?
View 1 Replies
View Related
Apr 24, 2014
Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
View 8 Replies
View Related
Jul 15, 2013
Suppose I have two tables with fields as follows:
Assigned Courses (Courses that employees should do)Employee number
Assigned Course Name
Fulfilled Course (Courses that employees have done)Employee number
Fulfilled Course
Now I need to do a query that tells me which employees haven't done. Is there anyway to do that that is not an exclude query?
View 4 Replies
View Related
Aug 18, 2013
I have a question regarding ranked matches in a query. My data is set up as follows:
Item, Customer, Shipping Plant
65432, 104321, 221
65433, 104321, 221 etc.
For some items and customer combinations, there are multiple plants that can provide the same item item to a customer. I would like to create a ranked column that puts a 1,2,3 and so on for these occurrences. So if a customer get one item from only one plant, the rank would be 1, but if a customer gets the same item from two plants, the first would be rank 1 and second, rank 2 (order doesn't matter).
View 1 Replies
View Related
