Pre-Define Query Based On Input Data?
Apr 21, 2014
I am looking to find out if I can build a "on the fly" pass-through query based on user predefined data.
Basically I am linking to a massive database. Access link to DB2 table. In order to make the system useable for the users they need to edit the query before it's ran. If not the query over the network takes around 5 minutes every time it's accessed and options such as filters and sorting are chosen. So if a user sorts one column and filters on another the query has now ran 3 times. Once to open, once to filter, and once to sort. That just took 15 minutes.
So If there is a way to build macro or form that asks them for specific information first and then modify's the query so that it only gets ran once that would be fantastic.
Example:Table has 7 fields/columns. Usually they will do 3 things. Pull back data from either a month or quarter. Then filter a column by it's content. Then sort by date of another date/timestamp column.
View Replies
ADVERTISEMENT
May 3, 2013
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
View 14 Replies
View Related
Dec 22, 2014
I have two tables linked by ID (Table1,Table2)
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10
1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
View 1 Replies
View Related
Jun 29, 2015
I'm attempting to build an import module so that my users can take data from different walks of life and import it into my tables. I'm doing this by setting up a module and allowing the user to specify which column data will come from when importing data. So lets say I have a field that is CustomerID in my table and user one pulls data from 1 place and in his excel or csv file, customerid is in field 1. Another user does the same thing and its in field 2.
How can i make some form of a loop that when I'm attempting to update data, i pull the correct column?
strSQL = "SELECT * FROM tblImportTable"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With rst
Do While .EOF = False
If IsNull(DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")) Then
[code]....
View 6 Replies
View Related
May 19, 2014
Is it possible, to input information from my continuous form into a web control form. at my job we a required to tract our jobs by equipmentid and job control number(jcn). When a job is done we have to upload the id and jcn into a website to tell it is cleared. I am looking for a way for that info to automatically be filled in when i load the website based on the job i have selected in my form?
View 1 Replies
View Related
Jun 2, 2006
I have a database that has several tables, each table has account numbers in
them. I also have queries set for each table to bring in desired
information. Is it possible to set up a form so the user can input an
account number and depending on which table the account number is in, that
query will run?
View 11 Replies
View Related
Oct 2, 2006
I have a database with the following information:
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
Thanks
:o
View 14 Replies
View Related
Sep 10, 2013
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
View 1 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
Nov 3, 2005
Ok this might be basic stuff i'm a bit dumb
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
Please help the dumb
View 3 Replies
View Related
Dec 31, 2013
I have a TABLE with the following data:
- EMPID
- LASTNAME
I have a form with the folliwing data:
- text box for user to enter EMPID, txtEMPID
- LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field...
- if the user enters an EMPID that is in the TABLE...display "Y"
- if the user enters an EMPID that is NOT in the table...display "N"
- a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
View 7 Replies
View Related
Sep 22, 2015
I need to be able to query the service dates for the bill. So I can query 1509 for the .billYYMM and .readdate and it will give me the TO Date. How do I in the same query tell the query to subtract 1 from the BillYYMM that I just entered and give me the read date for that field in that query? So Query 1 MBRHISTDETL.BILLMOYR and in the criteria box use [Enter the Bill Month Year YYMM you want to search for] will allow me to run the query at any time for any bill month year that I enter. So another field in that query is MBRHISTDETL.READDATE. So to get the FROM date and the TO date i need the following MRBHISTDETL.BILLMOYR (whatever I enter as input) and MBRHISTDETL.READDATE that corresponds and then I need [MRBHISTDETL.BILLMOYR]-1 and MBRHISTDETL.READDATE. The first gives me the TO date and the 2nd gives me the FROM date?
View 5 Replies
View Related
Dec 30, 2014
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Nov 30, 2014
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code:
Private Sub Command11_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
View 4 Replies
View Related
Apr 8, 2007
Hi
I need to define this query:
I have 2 unbounded fields : ShowMonth and ShowYear (Each field represent the month and the year of Date field)
And UserName field
I need to define query that shows the dates and the UserName accordding to ShowMonth,ShowYear and UserName field (Without SQL).
I will be happy if serious answers will be post
Thanks
View 3 Replies
View Related
Jul 31, 2007
Hi, I try to simplify what I did in Access as follows:
Name Of Company----Jan----Feb----Mac----Apr
------A---------------3-------3------4------5
------B---------------2-------3------3------2
------C---------------5-------6------6------5
------D--------------
Note: No data inserted for Company D for Jan-Apr
Question:
1. If the list of companies are longer (say thousands), how can I make a query to show company that make no transaction in Jan - April ??? AND
2.How can I make a query to show all the companies (with and without transaction) together?
FYI: For Qusetion 2, I can only get the filtered result shown for Company A,B & C while no Company D listed (coz no input/data??). Pls help me, TQ
Max
View 3 Replies
View Related
Dec 27, 2007
Dear all
i have a trouble to define a rank in query, i have a table named SALES
like below
region PRoduct qty
jabar A 10
Jabar B 20
Jabar C 30
Jateng A 50
Jateng B 40
Jateng C 35
i want to give a result in my query like below
region PRoduct qty Rank
jabar C 30 1
Jabar B 20 2
Jabar A 10 3
Jateng A 50 1
Jateng B 40 2
Jateng C 35 3
anyone can help me???thanks
regards
martell
View 1 Replies
View Related
Oct 17, 2005
I have a query which is pulling its data from a form, which in turn is pulling data from a table. When I select 0.82 on my form, my query runs fine. But when I select 0.826856 from my form the query does not return any result.
0.826856 is available in the table and the pull down list in the form. The data format for all my table, form and query is General and decimal places is set to 8.
Could you plz tell me why I am not able to run the query using 0.826856 ????
View 3 Replies
View Related
Oct 18, 2013
I have a query and a form, and what I want to be able to do is have the user type in within the form the parameters for the query.
The part of the query that will hold the parameters is based on an amount (formatted as Currency), but I want the user to be able to enter >10 , =<100 or >100000 and get the correct results.
I have already set up the query and the form with unbound cells which are then referenced in the query I've tried just one cell where the user would enter >100000 or tried two cells where one cell would be for >,< etc and one cell for the value (which is formatted as currency), but that didn't work either.
The idea is that you enter the parameter and value then click on a button that runs a macro to export the query based or the user parameters, but everytime I try it I get a box appearing saying Property not Found.
View 1 Replies
View Related
Jan 30, 2007
I have data recorded using an input mask,
"RF">L-0000;;*
to display data in the style: RFA-0001, RFA-0002 etc.
I have a make table query to join this field to another, to create a combo box look up.
Unfortunately, after I run the query, the only data from the input mask that gets imported is A0001, A0002 etc.
What have I done wrong? I guess the error must be in my specification for the input mask...
Any and all help gratefully received.
View 4 Replies
View Related
May 30, 2015
table name : Schedule
Field 1 = Vessel code
Field 2 = Voyage
Field 3 = ETA
Field 4 = berthed
Field 5 = Sailed
there is a query by using above table and data entry form based on that query.
need to add following facilitate
While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"
How could this be manage ?
View 4 Replies
View Related
Nov 22, 2005
I have two linked tables as follows:
Table 1
Main Service ID (primary key)
Main Service
Table 2
Sub Service ID (primary key)
Sub Service
Main Service ID (foreign key)
one-to-many relationship
I would like to query the tables, so you search on the Sub Service and it returns all of the Main Services it come under. I would like this query to run off an Input Box that will allow me to type in the Sub Service or a word.
I have managed to get the Input Box, by putting the following code into the criteria :InputBox («prompt», «title», «default», «xpos», «ypos»). But when I type something in the search fails. I have chopped and changed the fields I search on, but can't get it quite right.
Can anyone help me on what fields to choose and what I need to set in the criteria to get the search running properly from the input box?
Also is there any way to program Access or VB to return the results in a message box e.g. **** is a Sub Service of ***** - listing all main services it appears under?
Any help is most appreciated,
Thanks,
Luke
View 1 Replies
View Related
Sep 28, 2006
basically i have a textbox called
'mytext'
when my user types t1 in that textbox, and tabs to the next field, i want the value LHR - T1 to be placed inside 'mytext'
how can i do this?
View 2 Replies
View Related
Jul 9, 2013
I'm creating a database for work, and one of my forms currently has 8 subforms on it. For each individual account there can be one, none, or many of any combination of these subforms that will have information on them. I need the form to be able to show all of them, as someone else will be inputting all the information when the database is complete, but to make it easier I have set it up with checkboxes so that when a checkbox is checked, a subform shows, if not checked it doesn't show. I have very limited skills with VBA, so that in itself was an accomplishment for me.
My questions is: is there anyway in access to do it so that if a subform doesn't show, all the ones beneath it that do show will get bumped up so that it is easier to read/input data?
I have attached my DB and the appropriate form is "ICinfoT."
View 2 Replies
View Related
Jun 15, 2006
I need to make a query work so that when someone enters info into a master form, it will pull the data for that job onto other forms when the job # is entered. Thanks for any help.
View 1 Replies
View Related
Jul 2, 2015
I have to print a label quickly every time that the product hit the warehouse. The label has been created as a report linked to the query that will provide the info to the report. In order to make this report printing as quick as possible the idea is to scan the sample id from the product and once the label is printed scan the next sample and an on.
I'm not an expert on VBA but I have created the following scrip but the reports doesn't pop up.
Here is the code:
Dim SampleID As String
SampleID = InputBox("Enter Sample ID")
If SampleID > 0 Then
DoCmd.OpenReport "rptGRM_QuickPrintLabelDymo", acViewPreview, , "[Sample]=" & SampleID
Else
DoCmd.Close
End If
End Sub
View 8 Replies
View Related
