Queries :: SELECT Query Results Inconsistent - Not Getting All Records
Jun 20, 2013
I have a simple select query on a SQL table from Access. The query is:
SELECT tbl_Orders.OrderID, tbl_Orders.Approved
FROM tbl_Orders
WHERE (((tbl_Orders.Approved)=0) AND ((tbl_Orders.Completed)<>0))
ORDER BY tbl_Orders.OrderID;
The strange thing is that sometimes it pulls 34 results, and sometimes 38. From what I can tell, it should be pulling all 38.
What can I do to make sure it gets all the records?
View Replies
ADVERTISEMENT
Aug 3, 2006
I have a query and the works fine
The query is
Code:SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, IIf(subQuery1.TotalProgress=0,"",IIf(subQuery2.TotalDaysForDeliverable=0,"",FormatPercent((RawData.PercentComplete/subQuery1.TotalProgress)*(RawData.Finish-RawData.Start)/(subQuery2.TotalDaysForDeliverable)))) AS WeightedContributionFROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQuery1, [SELECT DeliverableDesc, SUM(RawData.Finish-RawData.Start) AS TotalDaysForDeliverable FROM RawData GROUP BY DeliverableDesc]. AS subQuery2WHERE ((RawData.DeliverableDesc)=(subQuery1.DeliverableD esc) And (RawData.DeliverableDesc)=(subQuery2.DeliverableDe sc) AND RawData.ReleasePeriod=ReleaseMonth);
I am now try to set that query as the record source in a sub form using VBA but it throws a syntax error, run time error ‘3075’
Code:strRowSource = "SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, IIf(subQuery1.TotalProgress=0,"",IIf(subQuery2.TotalDaysForDeliverable=0,"",FormatPercent((RawData.PercentComplete/subQuery1.TotalProgress)*(RawData.Finish-RawData.Start)/(subQuery2.TotalDaysForDeliverable)))) AS WeightedContribution " & _ "FROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQuery1, [SELECT DeliverableDesc, SUM(RawData.Finish-RawData.Start) AS TotalDaysForDeliverable FROM RawData GROUP BY DeliverableDesc]. AS subQuery2 " & _ "WHERE ((RawData.DeliverableDesc)=(subQuery1.DeliverableD esc) And (RawData.DeliverableDesc)=(subQuery2.DeliverableDe sc) And RawData.ReleasePeriod = '" & Me.Combo10.value & "' )" Me.DeliverableStatus_Contribution_Of_Tasks_subform .Form.RecordSource = strRowSource
How is it that the query works when I run it but when I try to apply it to a sub form it gives a syntax error, any ideas how to correct this
View 1 Replies
View Related
May 11, 2014
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
View 3 Replies
View Related
Jul 9, 2014
I work for a bank and have build a few little DB's for differant groups but my current project is a work flow tool for a department. I have it 90% completed and i still have to build in the reporting side. Part of the reporting is caclulating times and this is where i am having problem.
I have some coding built but i am getting inconsistant results, as an example if i am trying to calculate the total amount of time between [start Time], [End Time] then minus any [Stop Time].
Code:
Me.Total_Time = Format(Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])), "00") & ":" & Format(Int((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 - (Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])) * 3600)) / 60), "00") & ":" & Format(((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 Mod 60)), "00")
I have this on a command button, i have this same code for a few other calculations and in some cases it seems to be working i have just changed my start and stop fields along with what stop time to take out in each case. I have attached some parts of my DB, one of the modules, my main table and the form where i am running the coding.
View 4 Replies
View Related
Feb 5, 2015
I have a question where I need to effectively invert the results of a select query.
I have multiple tables in the database, but the 2 I'm looking at are TBL.Trip and TBL.TripDiary
A trip is logged in the trip table, and then an operator logs a diary entry against the trip. One of the options that the operator selects is when they receive an email back from the traveller, this is logged as a diary entry.
I need the query to look at the DIARY_Action field and select all records in the TBL.Trip that DO NOT CONTAIN an entry for Diary Action "4".
I can produce a select query that gives me the results to see all trips that HAVE had a response, with a simple IS LIKE "4" query in the action field.
If I change this to IS NOT LIKE "4", I get the results that I need, but duplication due to the various other "diary" entries in the table".
I simply need the inverse of the IS LIKE query but cannot see how to remove the duplicates?
View 5 Replies
View Related
Sep 23, 2014
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
View 1 Replies
View Related
Dec 3, 2013
Why I am getting duplicate results for some of my records in a query. I have unique values set to Yes. I have also validated that the tables I am using don't have duplicate data. SQL is below.
SELECT DISTINCT [tbl_Rewards Activity Report - By Member Number].[Member Number],
[tbl_Rewards Activity Report - By Member Number].[Last Four],
[tbl_Rewards Activity Report - By Member Number].[BAL ID],
[tbl_Rewards Activity Report - By Member Number].[Primary Name],
[Code] .....
View 1 Replies
View Related
Aug 18, 2015
I have a query that outputs results like:
Company ID | Data A | Data B | Data C
101 | results |results |results
102 |results |results |results
103 |results |results |results
104 |results |results |results
105 |results |results |results
In another Table containing additional company information, I have the primary key as the company ID, and I want to make the query that outputs the above table, auto-fill the blank fields in the existing Company information with the same headings as Data ABC etc.
However, I don't want the query to add full new records (which I think is the Append Query?), instead I want the existing company records have additional fields (Data ABC) added, with information from the Query added.
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
Apr 4, 2013
I have a Sales Table with below fields, i might have not set it up in the best way possible.
Consumer, Consumer_ID, SaleDate,Prod_Sl#,Prod_Type,Sale Amount
1. I need to update the sales price for each item sold based on category of Product Type, as we are tracking the product with Serial Number.
For an instance if 2 items of Category1 with Prod_Sl# as Sl1 and Sl2 and
2 items of Category 2 with Prod_Sl# as Sl3 and Sl4 are sold.
I need to update the sales price amount for these.
2. I want to accomplish this using a query.
View 1 Replies
View Related
Sep 19, 2013
I have a Union query as follows;
SELECT ALL *
FROM 1st_Lives
UNION SELECT ALL *
FROM 2nd_Lives;
1st Lives has 465,414 records and 2nd Lives has 151,852 records.
When I run the query I only get 604,976 records instead of 617,266 (I basically just want to add the two data items together).
View 1 Replies
View Related
Feb 5, 2014
I'm having a bit of a situation with a select query that I set up.
I have two tables, both with a list of serial ID's.
One is table field is formatted like this:
"B0340 13 453423 X"
The other is formatted like this:
"=B03401345342300"
I wanted a select query that could show me the records in a universal serial like this:
"034013453423"
This leaves out the "spaces", the "B" and the " X". (Couldn't find a way to make Access generate a new field with the serial ID correctly without a query).
So I used the "Mid" expression in a select query and it worked, it showed me the first table in the correct format.I wrote the same "Mid" expression for the other table and it worked too.But, when I ask for the results for both tables in the same query, it shows me much more records than I have(all duplicates). I figured out that when in table one I have 20 records, in table two I have 5 then the query shows 120 results (20 x 5).
This is the code in SQL view:
Code:
SELECT MainDatabase.[Transfusie Eenheid + Specification], (Mid([MainDatabase].[Transfusie Eenheid + Specification], 2, 4) & [MainDatabase].[Transfusie Eenheid + Specification], 7, 2) & [MainDatabase].[Transfusie Eenheid + Specification], 10, 6)) AS [BloedeenheidID MD], ScannedForms.BloedEenheidID, Mid[ScannedForms].[BloedeenheidID], 3, 12) AS [BloedEenheidID SF]
FROM MainDatabase, ScannedForms;
View 4 Replies
View Related
Nov 3, 2014
I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.
I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3
My query is:
SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header
FROM tblSomething
WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));
The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.
I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.
View 11 Replies
View Related
Dec 14, 2013
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
View 1 Replies
View Related
May 7, 2013
So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format
The format is 10 digits and it must start with a 0
Using Access 2010
View 2 Replies
View Related
Nov 8, 2012
I have split the database, with the back-end residing on the server. Only 1 other person is working in Access right now; she's verifying the data. Today she's working directly in the back-end, could this be the problem. If it is, I'm going to have to create a front-end for her quickly.
My problem is this...I'm working on queries and forms so that the scientists who will ultimately be using this application, won't be able to go in and inadvertently change something in the tables set-up. When I'm in the back-end main data table, it says I have 2723 records.
When I create a front-end query to query all the records in that table, it says I have 2160 records.
The input form that I created with most of the same fields as the query (created BEFORE I created the query...I'll have it pull from the query now instead of directly from the table) also says we only have 2160 records.
Using Access 2010
View 5 Replies
View Related
May 1, 2013
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
View 10 Replies
View Related
Sep 12, 2012
I have a db with 2 tables, one containing basic info FirstName, Surname, YearGroup, and another table with other data. I imported a list of names into the first table, firstname and surname only were imported, other field was left empty, other table was also left empty. I made a simple select query to pull all records from both tables and i get no results at all. None of the imported names show on the query results. what I am missing?
View 2 Replies
View Related
Mar 9, 2006
Hi,
I am a novice with Access and would like a steer with what I am sure is a simple issue but I can't find an answer. :confused:
I currently have 2 queries based on 2 separate tables.
Ops_Log_996_Query
SELECT [996_Table].Unit, [996_Table].Location, [996_Table].hiredate
FROM 996_Table
WHERE ((([996_Table].hiredate)=Date()));
Ops_Log_SQTU_Query
SELECT SQTU_Table.Unit, SQTU_Table.Location, SQTU_Table.hiredate
FROM SQTU_Table
WHERE (((SQTU_Table.hiredate)=Date()));
When run separately the first query returns 2 results and the other 1 result - fine so far.
I am now trying to combine the results for display in a report so I have a third query which takes its info from the first two -
SELECT DISTINCTROW Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate
FROM Ops_Log_996_Query, Ops_Log_SQTU_Query
GROUP BY Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate;
This displays the 2 separate records in the first 3 columns ok but in the last 3 columns the info in record 2 is a repeat of record 1. :confused:
Can anyone help me out please ?
Thanks, Oscar
View 2 Replies
View Related
Mar 22, 2015
I was beginning to think I had got the hang of creating queries on the fly in VBA. After several hours I have just learned that docmd.runSQL does not work for simple SELECT statements. I'm not going to worry about why that would be. I'm sure there's a jolly good reason.
So... I am able to dynamically create the SQL string for the SELECT statement that I need.
How do I run it? It just needs to return results as a datasheet so I can see them. I'm running Access 2013. I've been searching on this subject for quite a while and found many references to DAO, ADO, ADODAOD, YODELAEYYOUDELAYIO! and other things I don't yet understand ...
View 8 Replies
View Related
Mar 6, 2015
I have a query that is returning inaccurate "Date Completed" results. Here is the SQL:
PARAMETERS [Enter Start Date] Text ( 255 ), [Enter End Date] Text ( 255 );
SELECT DISTINCT [Qry BDM FA English Email Test Part 1].ADI_DISTRIBUTOR_ADVISOR, ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_DATE_COMPLETE D, [Qry BDM FA English Email Test Part 1].AGA_EMAIL, ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID, DateAdd("d",180,Date()) AS [Next Update],
[Code] .....
View 4 Replies
View Related
Jun 16, 2014
How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
View 2 Replies
View Related
Jul 29, 2014
I have a table with:
A dDataID (sequential auto-number)
B dDateTimeEntered (date/time field)
C dFactoryOrder (text)
D dSpecificationID (number)
E dStartWeek (text)
F dIncreasingDecreasing (text, values are limited to "Increasing", "Decreasing", or "NA")
I need a query to look up the last 9 values where C, D, and E match variables from a form, then tell me how many "Increasing" values are contained within that set. I want to store the final value in a combo box on the form. I tried writing the code and this is what I got - design view attached as a pic and SQL below. It looks like the "Top 9" is sorting on the Expr1 field, which isn't what I want, but I'm not sure how to force it to use dDataID or dDateTimeEntered to figure out which records to use.
SQL:
SELECT TOP 9 Sum(IIf([tblData]![dIncreasingDecreasing]="Increasing",1,0)) AS Expr1
FROM tblData
WHERE (((tblData.dFactoryOrder)=[Forms]![frmDataEntryDisplay]![dFactoryOrder]) AND ((tblData.dSpecificationID)=[Forms]![frmDataEntryDisplay]![dSpecificationID]) AND ((tblData.dStartWeek)=[Forms]![frmDataEntryDisplay]![dStartWeek]));
View 8 Replies
View Related
Mar 24, 2015
My statement below is current returning 4 records. Two of the records have the same GBPAmount value.
RequestID is the Primary Key
Code:
SELECT TOP 3 RequestID, GBPAmount, Currency, RequestDate
FROM PayRequest
WHERE (((Currency)="CAD Canadian Dollar") AND ((RequestDate)>#11/16/2014#))
ORDER BY GBPAmount;
Code:
RequestID GBPAmount Currency RequestDate
10207 8.17 CAD Canadian Dollar 03/02/2015
9874 33.82 CAD Canadian Dollar 20/01/2015
11327 109.58 CAD Canadian Dollar 23/02/2015
10495 109.58 CAD Canadian Dollar 05/02/2015
View 3 Replies
View Related
Mar 12, 2015
I would like to select records based on Minimum values of specific vendor.
Example:
MasterItemID Price PriceDate VendorID
1 2 2/5/14 30
1 3 2/5/14 31
1 7 2/5/14 32
2 3 2/5/14 31
2 2 2/5/14 32
So wants the all columns where price is minimum
Example: output required
MasterItemID Price PriceDate VendorID
1 2 2/5/14 30
2 2 2/5/14 32
View 6 Replies
View Related
Aug 6, 2015
I have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg
View 2 Replies
View Related
