Queries :: Conditional Query To Post Result In Field And Filter Result Records?
Mar 5, 2014
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.
View Replies
ADVERTISEMENT
Oct 12, 2013
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies
View Related
Sep 27, 2006
Hello, I have a combo box on a form which lists some names generated from a table.
I would like the selected name to be inputted into the 'critera' of another query called 'qryPBCustLevel' and for that query to be run.
I have tried to code this, but it is crashing at the point it trys to add the name into the query.
Can anyone help? Code listed below.
Sub cmbPB_AfterUpdate()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT DISTINCT tblTempPB.PB_NAME" & _
"FROM tblTempPB"
strOrder = "tblTempPB.PB_NAME;"
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PB_NAME] = '" & Me![cmbPB] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryPBCustLevel")
qryDef.SQL = strSQL & " " & strOrder
'Open the Query
DoCmd.OpenQuery "qryPBCustLevel", acViewNormal
End Sub
Thanks, Steve. :confused:
View 2 Replies
View Related
Aug 18, 2013
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
View 4 Replies
View Related
Sep 4, 2014
I have a query that allows 2 inputs for the same field ( batch no )
if you enter "pco0093" only you get 4 results
if you enter pco0094 only you get 4 results
both have the same part numbers is the results
When I run both together pco0093 and pco 0094 and use the "group" function on the field batch no I get 8 results ( as expected)
if I use the " where" function I expected to get the 4 results but with the " quantities doubling
What I am getting is more than 4 results but 5 with a part that was not on any of the single results
File attached ....
View 9 Replies
View Related
Feb 26, 2015
I have a created a query which I want to update a table. The field is called DTP. I have the Workingdays2 module in place as well as the Holidays Table. The dates (date only) are called Time in and Transaction date. I want the table to update records only if the entry in Completed is Yes. I have attached screenshot of what I have in query. I am no good whatsoever at VBA so I used the Query design view. It shows how many records will be updated (which is a valid number) and shows me which field will be updated. However when I run the query and then opend the Table the DTP field is empty.
View 2 Replies
View Related
Dec 21, 2013
1. I created a table that contains information about people and their details (mainly numerical info).
2. I created a form containing a command button and a label.
3. I have written a VBA script under the button so that when the button is pressed, the result of the calculation appears as the caption on the label.
My problem is...How do I get the script to run so it does the calculation for every record and places the result as a field in a query.
View 2 Replies
View Related
Mar 27, 2013
I have record like below:
id s q
1 11 11
2 14 15
I want to sum record 1 and record 2 and put them in next record.
View 2 Replies
View Related
May 2, 2013
Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?
Otherwise is there a way to make a master query ignore sub-queries if they return no records?
Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.
These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.
Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.
The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).
Here's an example of my sql statement for the modal queries.
Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate
HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))
ORDER BY Count([Toolbox Talks].TrendID) DESC;
- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.
View 6 Replies
View Related
Sep 26, 2012
I have a subform based on a query. In that query, I have calculated values for price+tax and another price+discount+tax. Now I have a conditional formatting that if the gross total is not equal to amount due, the amount due box will turn red. However even if they are equal visually, it still turns red. Ex. The gross total is 336.00 and the amount due is 336.00 also, so it should NOT turn red, but it does. When I click on the amount due control, the value turns into 335.999999195337. How do I tell access not to make 336.00 into 335.999999195337.
View 3 Replies
View Related
May 3, 2013
How can list the contents of one field columns in one row in a report.
Example:
ID: 1
A1
A2
A3
A4
Should be: ID1 - A1, A2, A2, A3, A4
View 3 Replies
View Related
Dec 5, 2013
I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.
exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)
"exp4" is the clean name of the song.
Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?
View 2 Replies
View Related
Sep 28, 2005
Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.
View 1 Replies
View Related
Nov 7, 2005
Hi,
I have a two column table and I want to display all records in the second column but I want it to be displayed in another single field.
Anyone has a trick on this?
Example:
Column1 Column2
001 Basket
002 Jar
003 Pillow
004 Hat
Result should be something like this:
Basket, Jar, Pillow, Hat
View 1 Replies
View Related
Oct 17, 2006
I have got 2 tables:
TAT table with fields: ID, SPB, SET, DAYSIN, HRSIN
SETS table with fields: ID, SPB, SET, DAYSIN, HRSIN, plus a few other fields.
Is it possible to do the following:
If (SPB.TAT = SPB.SETS) AND (SET.TAT = SET.SETS)
then store
(DAYSIN.TAT in DAYSIN.SETS) AND (HRSIN.TAT in HRSIN.SETS)
Thanks for any help with this.
View 4 Replies
View Related
Apr 27, 2015
How to get this one to display in a single column.
I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.
Customer Table with PK Customer_ID.
There are two tables with FK Customer_ID.
1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.
2. Table Lease2 - Has 1 field with 0 to Many records.
Goal:
The Type shows up in a single column.
Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)
Challenge:
Lease1 table has 3 fields that need to be transformed into a single column.
Lease2 table has 1 field to be appended to the single table.
Then, there is the column that identifies where the data came from based on the column name.
View 6 Replies
View Related
Oct 10, 2014
I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.
The only way I know how to do this is by an IIF statement :
Code:
SELECT [fld1], [fld2], [fl3], .....
IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4
FROM...
(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)
Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.
View 2 Replies
View Related
May 5, 2015
I have built a contacts manager based on an Outlook export (I know I can link the two programs, but I wanted to customize the contacts a bit further). I built the database in a way that phone numbers, addresses, etc. allow for multiple entries for each person under the same column using a foreign key. The only difference is a field called "Type" which designates the entry for Home, Work, etc. The issue I ran into is when I queried the data I get multiple entries for the same record. As you are all likely aware, Access repeats the data in a query for each different case. For example if Bob Jones has a personal and work phone number, Access will show the results in a query like this:
1. Bob Jones 555-555-5555 Personal
1. Bob Jones 555-222-2222 Work
2. Jerry Smith 555-111-1111 Personal
If I kept the data in Access it wouldn't necessarily be an issue. However, I want to be able to export the data in an excel file. How do I get Excel to return the data so it looks like this:
1. Bob Jones 555-555-5555 Personal 555-222-2222 Work
2. Jerry Smith 555-111-1111 Personal
View 3 Replies
View Related
May 14, 2007
I currently have the sql below...
UPDATE tbl_Node_List INNER JOIN qryUpdate_P1 ON tbl_Node_List.Zip = qryUpdate_P1.[Zip Code] SET tbl_Node_List.[Date Sent] = (SELECT [MinOfAudit Date] FROM qryUpdate_P1), tbl_Node_List.[Date Recv'd] = (SELECT [MaxOfAudit Date] FROM qryUpdate_P1)
WHERE (((tbl_Node_List.Zip)="35243"));
but I get an err.msg stating the operation must use an updateable query.
What I am trying to do is update my dates in 'tableA' with the max and min values stored in 'tableB'. I have read some of the posts but still can't quite get it. As always all help is appreciated.
View 2 Replies
View Related
Jun 2, 2013
1 I have a delivery method column and I want to only show 1 type for each method. How would I do that? Is it in expression builder?
View 1 Replies
View Related
Mar 17, 2013
I'm struggling with the correct syntax for this, but ideally what I want to achieve is:
SELECT (SELECT Count(Status) FROM P_T WHERE 'criteria are met') / (SELECT Count(Status) FROM P_T WHERE 'different criteria are met')
They are both fairly simplistic select statements, so I imagine this is not that difficult to achieve, but I'm not sure how to structure the query.
View 6 Replies
View Related
Jul 19, 2013
there is a query which i made which counts data from two tables RAUTH AND PERS
SELECT RAUTH.coy, RAUTH.Auth, Count(RAUTH.coy) AS Held
FROM pers INNER JOIN RAUTH ON pers.coy = RAUTH.coy
GROUP BY RAUTH.coy, RAUTH.Auth;
THERE IS A BLANK FIELD NAMED HELD in table RAUTH i want to update the held result which i quired into HELD fiedl of RAUTH table where COY (Field) is COY field of PERS table
RAUTH table structure
Coy Auth Held
Tata 12
Dell 11
HP 21
Opera 11
Bangour 10
pers table
Persno Name Coy Qual
124578 Gita Tata Bsc
235689 Sonu Dell BA
748596 Kiran HP BCA
459687 Sony Tata MCA
965214 Baiju Bangour CCNA
963656 Ashok Opera MCA
View 4 Replies
View Related
Nov 23, 2004
I have a query_ReimburseResult
that counts the yes/no answers in field Reimburse from tbl_Survey.
How do I take the results from the query and put it into a field in another table?
I have tbl_Result and a field called ReimburseResult. I set the type to long integer and then what?
thanks!
View 2 Replies
View Related
Jun 15, 2006
Hi
I have a Form called Products. Each product is uniquely identified with a primary key called [ProductID].
Products contains a calculated field called [UnitsOnHand]. [UnitsOnHand] gets its values from a subform called ProductTransaction that exists within the Products Form.
ProductTransaction contains a field callled [Received] . At the moment [UnitsOnHand] will add up all the values it finds on the [Received] field to derive the [UnitsOnHand] value for that product at any particular time ie =Sum(nz([Received])) on [UnitsOnHand] .
I also have a Query AutoSell that outputs a value for each [ProductID].
How can I use these values for each [ProductID] generated by the Query to substract from the values on the [UnitsOnHand] calculated field on the Product form.
ViRi
View 1 Replies
View Related
May 19, 2013
Have a query which looks up an address using the text input into a textbox by the user.
What I'm now struggling with is getting the query result into the textbox.
Am still new to access and am hoping this is something fairly simple.
View 2 Replies
View Related
Dec 26, 2013
I want to make a form which will allow me to get data from a query and search using 3 fields parallel.
Attached the database in 2010 format.
Password of the db is "nolimit".
The query PTM & Equipment should be bound with the form and
If I enter month, PTM and equipment need rest 2 fields displayed.
Like choosing the above 3 fields from the drop down , the next 2 text box should display the rest two fields.
View 3 Replies
View Related
