Queries :: Query To Pull ALL Records Whether Blank / Zero Or Value
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?
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010 John Doe 1006 P 21-JAN-2010 John Doe 1006 CLERK P 01-FEB-2011 John Doe 1006 F 01-MAR-2011 John Doe 1006 P 01-APR-2011 John Doe 1006 CLERK F 01-JUL-2012
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));
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
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.
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried: 1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing! 2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
Most employees have 10 to 15 rows. I only want to pull ALL rows for employees ONLY if there is a distinct count from DATE_PAID greater than 1. There are cases where an employee might have two pay checks generarted for the same PAY_DATE.
I have 2 tables with the same field names, but different data. I need to check if one person is linked to different companies.Both are linked with CompanyID. When I look for a person via the last name (field is called LastName), it should show me from both tables the rows that this person is in. how can I construct this kind of query?
P.S. I want to select all the not just a few from both tables
I've got a query that uses a key from one table to pull back records from another.
My question is...
On the table with the key there is another field that I want to use to pull back data from the same table from above.
A bit more info...
Table one holds colleagues details Table two holds records
Within table one there are 2 different fields that hold different ID's for the same colleague, currently I run two different queries to pull all data - I want to know if I can simply run one query that will return every thing?
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010 John Doe 1006 P 21-JAN-2010 John Doe 1006 CLERK P 01-FEB-2011 John Doe 1006 F 01-MAR-2011 John Doe 1006 P 01-APR-2011 John Doe 1006 CLERK F 01-JUL-2012
I have a form that is used to book a new event for a client who is already in the database.Within that form I have a subform that is based on a query which displays information from that client's previous event. I did this using a solution found in the following thread: URL...It is based on pulling the second to last record that is related to the current client. It works perfectly when booking a new event that has taken place on a previous date. However, if the previous event occured on that same date (but at a previous time), it doesn't register.
I would prefer a query that would pull the record previous to the current one, instead of pulling the second to last record out of all that client's events.it would also mean that if more events are booked, then a past event is opened in a form, the sub forms in that form will display the event just prior to the current record.
I have a query built that gives me info per employee. I built a question into the criteria that ask for the employees name. When a person types the name results are given. If the person mispells the name, results are not given.
I have everyones name on a seperate table. How do i create a pull down menu on the query to choose the names?
I have 9 seperate tables - each of the tables has similar headers
Claim # Agent Pass/Fail Request type Record Date
I have built relationships between all the tables. I am attempting have a singular query be run based on start and end dates under "record date" . I have the criteria already set. But when I run the Query no information is pulled. How do I get the query to pull this data from all 9 tables?
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 need to know a query to pull information from two date fields into one. I have a BuriedStartDate field and an AerialStartDate field. In a third field I have PlacementStart. I would like to put the date into the PlacementStart field which ever is the oldest date bewtween the BuriedStartDate and AerialStartDate. It has been awhile since I have done any queries on Access 2007. I am completely self taught in Access.
The 'key' fields are a composite key of primary key fields from other separate tables. I have a form that generates a new 'Key 1' record and I would like to make a query that creates all possible 'Key 2' and 'Key 3' results (new combinations) while key 1 stays the same. How would I go about doing this? I know I can be done.
I cannot really give any real data as I haven't received it yet.
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 have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code: SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID WHERE (((tblMaterialSpecifications.ActiveInactive)=-1)) ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
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.
I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code: =IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
This works for the records with fields that have data.
Code: Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
all using access 2010. I have a date field. I need to write a query to pull out dates with current year only. ex data: 1/1/10, 1/1/11, 2/1/12, 2/1/13. Need to pull all dates with current year which would be 2/1/13. Tried in query criteria of the date field: =Format(Now(),"yyyy") I received data mismatch. this is a date/time field.
I am trying to run some diagnostics on my database. I thought a good place to start would be to look at all of the tables, the number of records each table holds, and each table's size. I have been able to find plenty of code on the web that loops through each table and provides the aforementioned data points, but the code only looks at local tables. SInce the database is split, I have a ton of tables linked to the back end that I am unable to get stats on. Any query or any code that can pull back end table stats?
I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.
What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.
So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.
I have a text file linked to this database, and I'm trying to create a Query that will pull all of the data out of this text file, and add a rank if two of the values match in multipe records.So in other words, when multiple records have the same [JOBNBR] , I want to evaluate the [TIMESTAMP] value to see if it is the smallest one, and so on.I don't want to create multiple queries to do this, is there any way to do this? Here's what I have:
Code: DDR: (Select count(*) from tblMyDataImport Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR] )
