Queries :: Pull From A Second Column If The First Is Null
Aug 6, 2013
I have 1 query that connects 4 of my tables through a tracking number. I am trying to make it so the value (DStatus) from DTable will populate where a (DStatus) is provided, but where there is no (DStatus) the column is filled in with a (SStatus) from STable. There is always a SStatus for each tracking number, but not always a DStatus, where there is a DStatus, it is more accurate.
Can I do this all in 1 query, whether I have to make a new column within my query for this to work, whether I need to have my query output into a table first.
Is there something I could type into the Criteria field of my query.
I have tried using Nz([DStatus],[SStatus]), an Iif Statement, the IsNull function, etc with no avail.
I created a a table with multiple columns. For example the table looks like as follows:
Table name - Observationcode Code Value Description CCH 1000 Heavy Crack CCM 750 Medium Crack CCL 500 Low Crack
I created fields on the subform - frmsubmain1 Field names are as follows:
Observation Code Value Description
I created combobox for a field called "observation code" so that the above mentioned table "observationcode" appear as a pull down menu to select from multiple lists. In the property section I bound the feild "Observation Code" to " column 1" to record the item in the column 1. I reapted this process for the fields "Value" but this time I bound the field to column 2 to show the value rate or value for the selected code. Again I reapeated this process for field name called "Description" but this time I bound the column to the column 3 to record the description for the corresponding code.
On the main form - frmmain
I created a field called Rate to calculate the total value in the subform "frmsubmain1" the validation rule looks like as follows: =sum(Forms![frmsubmain1]![Value])
None of these seems like working except for the first one "Observation code". Observation code is looking into the right column and registering the right code but not the other two fields are not looking to the right columns, instead they are looking into the firts coloumn. I don't know why. Can anyone help me resolve these issues. I appreciate your help. Thanks.
I have fields on the main menu where folks put in a start and end date prior to running a report. I'm doing a monthly report that pulls current month, and two prior months of data. For example, the user selects a date range of 10/1/14 to 10/31/14 the report will show August, September and October results.
I need the column headings to change each month depending on the date range they select to run the report so in November they will pull 11/1/14 to 11/30/14 and get September, October and November.
The headings for the columns would then adjust.
I put an unbound text box with the formula as follows in the control source of the report page header to pull the month that is equal to the current month minus two (I'm just showing one of the column headings formula):
Okay so I have a column called 'totalincome' on TblCashflow. There are 60 records of TotalIncome (represents 5 years or 60 months) per asset that I have on another table, TblLoanGeneral.
What I need to return is the LAST value from totalincome that is not null. For example, we may only have entries in the 60 records that go to row #35, or 50 or whatever. When I use the Last criteria it gives me the 60th record (in example would give me a 0), and when I use the Max criteria, it gives me the largest income value regardless of location(would be 50,000 in example). What I need is really where the "last" income amt is >1 (20) however, when you set last, it only considers row 60.In the example below I would want 20 to be returned.
This is the function i'm using for summing a listbox column:
Function TonSum() As Variant Dim I As Integer, J As Integer, ctl As Control Set ctl = Me.lstDrivera2 J = ctl.ListCount - 1
[code]...
The problem i have is if one the records in the listbox doesn't have a value (is null) then Access gives me a 'Type Mismatch' error. How to treat the null value so it excludes it from the sum?
I've been trying to populate a field in a form with information, but only if the field is blank.
What I am trying to do is this.
I have a continuous form displaying all of our principals, however when our principals are on leave I want to populate the field with the "acting principal". I have set it up so the field grabs the acting principal first but it is blank for several schools. For these schools where there there is no "acting principal" I want it to grab the substantive principal automatically and populate the field.
I have tried Is Null, Is Not Null, NZ, IF, Where and Then statements but to no avail.
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
i have a columns as 1. contactname, 2. firstname 3. lastname 4. email and in this columns some emails are not matching with the contactname or some time firstname or some time lastname so i need the to find out the un matched contacts from the database.
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
I have a text field in a Table and on a Query called "Notes" In that field that has data like below:
[04/02/2015:BD] Project is to be assessed by Solutions Planning [03/27/2015:BD] Project prioritized [03/14/15:BR] Entered to system
Im trying to find a way to pull just the most recent line of text, in this case
[04/02/2015:BD] Project is to be assessed by Solutions Planning
into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.
I have two tables, one table Data has member(SUBSCRIBER_ID) data including service rep(UserName). Another table ServCoord has data including service rep and their manager. What I am trying to do is pull 5 random accounts per service rep. I can pull 5 random accounts, but not per service rep. I have currently 77 service reps. Is there any way I can pull 5 random accounts per service rep? Here is my starting point so far.
SELECT TOP 385 [Data].SUBSCRIBER_ID, Rnd(1) AS Expr1, [Data].UserName, Rnd(Len([UserName])) AS Expr2 FROM ServCoord INNER JOIN [Data] ON ServCoord.ServCoord = [Data].UserName GROUP BY [Data].SUBSCRIBER_ID, Rnd(1), [Data].UserName, Rnd(Len([UserName])), [Data].SUBSCRIBER_ID, [Data].UserName HAVING ((([Data].UserName)<>"NULL")) ORDER BY [Data].UserName, [Data].SUBSCRIBER_ID;
I have 2 reports that use a separate query to pull their data. One Query is called "Outdoor Lighting by Model Number Full Query".It is very simple, pulling just 4 pieces of data, SQL code is as follows:
SELECT DISTINCT [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity] FROM [Call Log] WHERE ((([Call Log].Date) Between [Forms]![Date Range Entry]![Start Date] And [Forms]![Date Range Entry]![End Date]) AND (([Call Log].[Product Type])=4)) GROUP BY [Call Log].[Model #] ORDER BY Sum([Call Log].Quantity) DESC;
I run a report named "Outdoor Lighting by Model Full Report" which takes the above query. The detail portion of the report only has 3 sections:
Model #, Quantity, Percentage. Quantity I have showing "Sum Of Quantity" and Percentage is showing "=[Sum of Quantity]/Sum([Sum of Quantity])" Therefore, for a particular model # it gives me the number and then calculates what percentage that number is of the total. Works fine.
My second report is laid out exactly the same, except it pulls data from a different query that only returns the top five results. The code for that query is as follows:
SELECT TOP 5 [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity] FROM [Call Log] WHERE ((([Call Log].Date) Between Forms![Date Range Entry]![Start Date] And Forms![Date Range Entry]![End Date]) And (([Call Log].[Product Type])=4)) GROUP BY [Call Log].[Model #] ORDER BY Sum([Call Log].Quantity) DESC;
The second report, appropriately named "Outdoor Lighting by Model Number Top 5 Report" only shows the top 5 and then gives a quantity and calculates a percentage. My problem is that the percentage is calculated based off of the total of just the top 5 query. I need it to calculate based off of the total of the full query.
I currently have percentage doing this "=[Sum of Quantity(Top 5 Query)]/Sum([Sum of Quantity(Top 5 Query)])".What I want it to do is this "=[Sum of Quantity(Top 5 Query]/Sum([Sum of Quantity(Full Report Query)])
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 a query that pulls data from the following fields in 2 different tables:
Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20 Area2FloorPrep (tblFloorPrep) Area2Size (tblInstallationAreas)....
All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concatenate the data in the 20 fields and concatenate the size of the areas next to the appropriate concatenated floor prep description. Is there a way to do this without coding?
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 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 3 tables invoice, customer and items. I need to create a query that will get the next invoice number from the invoice table and mark that invoice number in the items table for a set customer where the invoice field in the items table is blank.
The invoice table is now set up as a autonumber, therefore I need to append to get the last new number.
The items in the item table are unique and this is why I want to mark the invoice number in the item table field invoice.
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 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
