Queries :: Query Records With Same Value In A Field
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.
I use an append query to insert this new record into the table EVENTS_MAIN.
I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.
Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.
Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
I have a query by form that has the criteria Like [Forms]![FormName]![ControlName] & "*" for each field. However the problem is that if one of the records have at least one blank field then the whole record wont show up in the query results. For example if there is a record with Fullname, and Age filled in but Address isn't filled in then if search Adam into the query by form the record wont show up because the address line is blank.
To make records that have a blank field show I know I could use Like Forms![FormName]![ControlName] & "*" Or Forms![FormName]![ControlName] Is Null but when I have used it, it has corrupted the query because I think if you use If Is Null many times in one query it becomes too complex for it to process.
Also, The other method is to use Nz in an expression but I cant do that because I want the query results to show up in a form where you can edit the records and the error message Field is based on an expression and can not be edited comes up if you try to edit the records. Anything else I could put into the criteria to show records that may have a blank field?
I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...
At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter
Code:
SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator FROM MatterContactsMade GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator])) ORDER BY MatterContactsMade.MatterId;
I need to write a query that shows all records if any 'L' field starts with D. I have written this, but it's only pulling records if L1 starts with D.
SELECT Item, Description, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12 FROM Table5 WHERE ((L1 LIKE 'D*') OR (L2 LIKE 'D*') OR (L3 LIKE 'D*') OR (L4 LIKE 'D*') OR (L5 LIKE 'D*') OR (L6 LIKE 'D*') OR (L7 LIKE 'D*') OR (L8 LIKE 'D*') OR (L9 LIKE 'D*') OR (L10 LIKE 'D*') OR (L11 LIKE 'D*') OR (L12 LIKE 'D*'));
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example: For today, we would use 4085-0001, 4085-0002, 4085-0003, etc. For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
Using a database 2010. I have a monthly import from a spreadsheet to one of my tables. Sometimes it has the same previous data from prior month or months that has not changed. I don't want to keep appending this data to my table if none of the fields have changed.
Is there a way to append data to table only if fields have changed in an append query? Ex. (Name, Address, PhoneNum, Rate) If all fields are the same; don't append. If one or more fields are changed; append.
I am linking to an SQL database that is designed in such a way that the problem description is recorded in multiple records. See a simplified example below. I want to run a query that will merge the information in the PROBLEM_DESCRIPTION and return only one record for PROB_ID 55678.
PROB_ID ORIGINATOR DATE PROBLEM_DESCRIPTION 55678 Jim 01/01/2015 While cleaning my computer, I noticed 55678 Jim 01/01/2015 the screen was cracked. I called 55678 Jim 01/01/2015 the IT department and they said I had 55678 Jim 01/01/2015 to write a work order. However it 55678 Jim 01/01/2015 was rejected without reason.
All. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?
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...
I have a field in an MS Acess query that has records like - 1,000; 534; 769.13; 0; 654; 0, etc. It can also have few null records.I want to calculate the average of all records which are greater than zero. Ignore zero values or null values.
I have a field that is text format. However many of the records in that field contain numbers.I want to delete all the records that are numbers in this text field, leaving only records with textual data.
I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:
I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!
I am working with an inherited database. When this database was created, a large amount of data was imported. Over the course of time, I have added additional fields for tracking information. One such field is "Date Started."
Unfortunately, there are almost 500 records without this information and that is skewing some report results.
I would like to do is insert the date of 9/9/1999 into all records that have no data in this field. (This date is well before the creation of the database and would serve to indicate old records, whether or not they are still active.) Copy and pasting isn't working, and I can't do a find and replace, since there's nothing to find.
In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.
Currently my query looks like this;
As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.
I am putting the finishing touches on a DB and have come up with a a problem which I cannot solve. I have code on my data entry form that concatenated the days and times of up to five days. That is : [Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") & " " & ";" and so forth for five days. It has a complicated (for me) nested IIF statement and it works fine on the form. But I have now transferred the data from an old DOS DB and I do not want to go through almost 5K records just to update three fields.When I tried to do it in an update query, it said that the string was too long for the update. My update is:
how to update the table if a field is blank without clicking through all 5K records? I have tried simple concatenation, but I usually get a lot of extra @ in the field.
How do you write a MS Access query criteria that looks for records like the current year only for a date field ? I tired Like *Year(Now()), it did not work.
I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.
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.
