Self Generating Query String Based On Query Results?
Jan 3, 2008
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
View Replies
ADVERTISEMENT
Jun 3, 2014
If I use this query I get the results what I want
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null))
ORDER BY Count(tblMainTWTTPSheet.txtRootCause) DESC;
But What I am trying to do is on a form select dates and generate data in this query. which doesnot happen even if I use the following query.
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]))
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));
View 4 Replies
View Related
Mar 2, 2005
Hello All,
How can I create reports in Access based on dynamic queries? I did a lot of search on this but couldn't find anything reasonable.
Any help to get me started will be extremely appreciated.
Thanks
View 1 Replies
View Related
Jun 23, 2005
Hello, I have a query that returns a list of courses undertaken by certain employees. They are in the format NPTC CS 30; NPTC CS 31; NPTC 33; NPTC FTC 38; NPTC FTC 40 etc...
Anyway what I need to do with this is convert these results to a string and manipulate them in such a way that they can be displayed in a text box on the back of an ID card in the following format:
NPTC CS 30, 31, 33, NPTC FTC 38, 40... etc.
Because there is not enough room on the back of the ID card to use a subform to just display them in a list...
View 3 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Apr 8, 2008
Hello again.
I have a drop-down list that contains string values. Is it possible to construct a query that will sum the total number of times that the string value is selected? For example,
String Value Sum
================
Chicken 3
Beef 9
Pork 2
I have been trying to work on this for a while and cannot figure it out. Can someone shed some light on this issue? Thanks.
View 1 Replies
View Related
May 2, 2014
I have a queries that do all the calculations and dumps the output to Query X for all different types of customers. At the moment I am required to generate a report for each of the customers and send it to them, manually.
End Goal:Initiate a Macro (at a given time at a given frequency) that would run a process to generate different reports for all different types of customers using a standard report template. I am also trying to avoid having to create a report for each customer (as the customer base grows, the report count will grow) so looking at something that would look into Query X and generate # of reports depending on number of customers.
View 6 Replies
View Related
Sep 10, 2013
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
View 1 Replies
View Related
Apr 17, 2008
Hello,
I have a query (used in a combox) with a field called "UserID" and in the query I would like to have the UserID field get it's criteria from the "User" field on a hidden form.
I have it for the most part however, I have two users (myself and another) that when we use the combo box we se "All" the records in the query.
I was thinking something like:
IIf([Forms]![frmUser]![user]= [User],true, "*")
I hope this makes sense.
Thank you
View 2 Replies
View Related
Jan 12, 2006
G'Day:
I have tried numerous things to try and create a pop up message or form to alert users of a timing conflict in a scheduling program.
I am not real familiar with codes and am not sure how to handle this.
I used Jason Browne's web site to help me construct a query that will list a result of time conflicts (ie some project is scheduled to occur before another one is completed).
What I need to have happen is the alert message or form opens when this conflict occurs but does nothing when the timing is ok.
I am not very familiar with code and am having a road block mentally. All help is appreciated!
THANKS
View 3 Replies
View Related
Aug 24, 2006
I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field? Something like, if field1 = "expired" then lock the table? I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
View 1 Replies
View Related
Nov 5, 2013
I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use.
I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value.
View 13 Replies
View Related
Oct 29, 2014
I have 2 form controls one a combobox and the other a text box. The text box select the site (txtLocation) where the user can enter part of the name of the site and all sites with those characters are returned. I've done this by adding:
Code:
Like [Forms]![frmSearchDB2]![txtLocation] & "*"
into the criteria on the Site field in the query design editor.I also want the combobox to have an affect on the query. I want it to query on user status. However if the combobox reads "All Users" I want it to return all status's and all null values. In the criteria field I put:
Code:
iif([Forms]![frmSearchDB2]![cbxUserStatus] = "All Users",like "*",[Forms]![frmSearchDB2]![cbxUserStatus])
It kind of works but no null values are pulled back. Should it be an expression?Do I need to do it in VBA?
View 1 Replies
View Related
Dec 11, 2013
I'm trying to track daily production at a manufacturing company running many different processes at different locations each running multiple parallel "lines." The tricky part is that the number of lines running and the shift schedules e.g. 2shifts 10hrs/day 4days/week or 3shifts 8hrs/day 5days/week change frequently for each process.
Right now I have a form for process data that specifies the schedule and number of lines running each day. Then I'm running an append query to a "production" table that generates blank production records associated with each item made in each process for the correct shift/line combinations.
Up to now I've been manually changing the date on the append query each time I run it. Then I have a seperate query for each process that pulls out the production records for each day. My problem is that the preferred interface for production data entry is a spreadsheet with the following layout:
--------DAYS LINE 1 DAYS LINE 2 SWING LINE 1 SWING LINE 2
ITEM 1 100
ITEM 2 2250
ITEM 3
which changes each time the shift/line schedule changes for each process. The only way to achieve this layout I know of is a crosstab query which isn't updateable. Ideally, each day the manager will specify the shift schedules and forms will be automatically generated with the correct structure and sent to the process supervisors. I'm open to different form layouts and even redesigning the database completely.
View 2 Replies
View Related
Apr 1, 2014
I have got a form (name: SearchForm) that displays results of a query (name: AircraftSearch). It is a continuous form displaying multiple results of a search done by the query. I need to be able to send an e-mail to multiple recipients chosen from results displayed on the form.
One of the form's field (a text box called: EmailToOperator) is containing e-mail address to an aircraft operator selected by the underlying query. I need to be able to place a check box button (or something similar) that is going to select the e-mail address. The tricky part is to have multiple check boxes allocated for each record displayed on the form and have them working independently.
The second task is sending a one message (via Microsoft Outlook 2010) to chosen multiple recipients (with no attachments) having the recipients' addresses not visible to each other.
View 7 Replies
View Related
Jun 24, 2014
I would like to change the background color of a field that is the result of a Unique Values query. I am trying to get a list of invoices where all the line items are approved. I can't seem to get it to work the way I want because if even one invoice line item is approved it will show up as approved.
Is there a way to change the background color of the invoice field to red if ANY of the Approved line items are = False
View 7 Replies
View Related
Jul 31, 2013
I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business
In this query, I am trying to use parameters to filter the results based on these three fields i.e.
true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.
false, true, false would return all records where only parent is true, and the other fields do not matter.
View 2 Replies
View Related
Jun 23, 2006
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs
JobID (J000001, J000002, etc)
Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc
Consultant: (Joe, Terry etc)
ObjectID: (RoberI, SmithJ etc)
Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001"
as [JobNo] FROM J000001
UNION SELECT ObjectID, Consultant, Status, "J000002"
as [JobNo] FROM J000002
UNION SELECT ObjectID, Consultant, Status, "J000003"
as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
Any help greatly appreciated.
View 2 Replies
View Related
Feb 9, 2006
I want to know how to generate a result from a query in a control of a form? For exampel I have made a SQL count function in a query and I want to use this result i a control of a form.
View 1 Replies
View Related
Jul 3, 2014
I am trying to run simple query make a chart in report through this query but its not generating chart?
SELECT [Unsafe Act Unsafe Condition].[Inspection Audit], Count([Unsafe Act Unsafe Condition].[Inspection Audit]) AS [CountOfInspection Audit]
FROM [Unsafe Act Unsafe Condition]
WHERE ((([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmSafetyReportOut]![startDate] And [Forms]![frmSafetyReportOut]![endDate])) OR ((([Unsafe Act Unsafe Condition].Department)=[Forms]![frmSafetyReportOut]![cboDepartment]))
GROUP BY [Unsafe Act Unsafe Condition].[Inspection Audit]
HAVING ((([Unsafe Act Unsafe Condition].[Inspection Audit]) Is Not Null) AND ((Count([Unsafe Act Unsafe Condition].[Inspection Audit])) Is Not Null));
View 1 Replies
View Related
Feb 1, 2006
Hi guys, I am trying to create a query collecting data from 4 tables as well
as calculating totals delivered but it's hurting my head trying to work out
all the joins can anyone help please?
The 4 tables are as follows:
Orders
=====
OrderNumber
CustomerName
CustomersRef
OrderDetails
=========
Order_Items_ID
OrderNumber
ItemNumber
Qty
ItemType
Description
Deliveries
=======
DeliveryNoteID
OrderNumber
Date
DeliveryDetails
==========
DeliveryDetailID
DeliveryNoteID
ItemNumber
QtyDelivered
The relationships are pretty straight forward, OrderNumber on Orders and
OrderDetails are linked as are DeliveryNoteID on Deliveries and Delivery
Details.
I want the query to list every single item for all orders as well calculating
how the quantity remaining to be delivered based on deliveries so far and Qty ordered.
So I want to create a report that pretty much just displays:
Orders.OrderNumber - Orders.CustomerName - Orders.Customer_Ref -
OrdersDetails.ItemNumber - OrdersDetails.Qty - OrdersDetails.ItemType -
OrdersDetails.ItemDescription - Deliveries.DeliveryNoteID - Deliveries.Date -
DeliveryDetails.TotalQuantityDelivered
Can this be done as one query or do I need to join two queries together?
It's hurting my head :(
View 1 Replies
View Related
May 17, 2015
I've come across a problem that doesn't make sense to me. I have a form with a combo box on it. Each time the combo box is used or the form is moved to another record it triggers an event to update the text boxes.
I get the information for the text boxes from a query. Everything was working smoothly until I decided to add a text box to the form to be filled in. My thought was I add the column from the query to the code and it will update. No dice. It doesn't recognize any information in the query. But when I run the query as a standalone it sees the text. When i open the table that holds the text, it is still there.
Code:
Private Sub cboDoctor_AfterUpdate()
'update doctor fields on the update of the combo box
If Len(Me.cboDoctor) > 0 Then
Call DoctorName_Change
[code]...
When i step through the code it shows values for each of the columns except for column 11. It reports as 'null.' Things I've tried:
-Updating the combo box by reselecting the value for the record.
-Restarting DB.
-Changing from one record in the combo box back to the original record.
Is there a limit of columns that can be used? Column 10 works just fine...
View 3 Replies
View Related
Dec 22, 2014
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies
View Related
Apr 29, 2015
I am trying to create a database to link to an online auction site (not ebay) and I want to create a query that randomly generates auction end time and auction length for all listings. I have tried int(rnd()) and various versions but can only generate the same number for all fields in the query.
View 1 Replies
View Related
Mar 13, 2008
created a form with several Drop downs, S/N, Location, Manufacturer, Model, Model Number, and Owner.
All the drop downs are based off of forms, i queried the main inventory to have this info and a couple peaces of information. applied the relationships.
If i try to make a macro filtering the conditions, if i leave any of the fields blank nothing comes up in the search.
is there a VB code that i can use that wont include "null" fields or will this not work with 6 different filters applied all in one macro.
furthermore after the search i want the ability to print the search results using a report design.
is this possible?
View 2 Replies
View Related
Sep 9, 2013
I have a library function that will allow the user to nominate a query (as one of its arguments) in the calling application which must have an email field. The function will then Do Loop the email field, concatenating it before creating an email and addressing it. The intended functionality is that a developer can easily create a group email, just by creating a query.
This works fine if the query is filtered "statically" - i.e. I specify which group of people by typing in their "Site_ID" in the criteria. However I want developers to be able to creating dynamically filtered queries (perhaps by the group's ID on a calling form). Within the query (to test it), the filter is therefore [Forms]![Test Function Calls]![Site_id]. When I run the code, I am then presented with "Run-time error 3061: Too few parameters. Expected 1". The code in question is:
Dim rst As DAO.Recordset
Dim stTo As String 'one of the function's arguments received from the calling function.
Dim stToString As String 'the built up concatenated emails
Set rst = CurrentDb.OpenRecordset(stTo, dbOpenDynaset, dbSeeChanges)
[Code] ....
View 6 Replies
View Related
