Queries :: Way To Construct A Query From Selected Inputs
Nov 27, 2014
I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
View Replies
ADVERTISEMENT
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
Sep 4, 2014
I would like to run a query that allows we to enter as many criteria in one field as I want
For example
I have an order for wheel for a car order no "1234" then another order comes in for another wheel order no "23456" not the same wheel type
Each wheel could contain 10 parts to make the wheel both orders could contain 5 parts that are the same
What I want to do is enter as many order numbers as I want then when I run the query and it groups all the parts together for me to order in that query.
View 8 Replies
View Related
Mar 29, 2007
how do i use conditional statement in sql? im using access 2003 as frontend and backend.
i would like to count the number of records in a table (ex. ENROLL) where there is only 1 instance of any value in a field (ex. STUDENT_NO) plus another condition where a given value (ex. 1) should exist in another field (ex. SECTION).
example
Table name: ENROLL
STUDENT_NO SECTION FEE
16 ----------- 1 ------ 75
16 ----------- 2 ------ 75
17 ----------- 1 ------ 100
18 ----------- 2 ------ 125
19 ----------- 1 ------ 75
19 ----------- 2 ------ 75
19 ----------- 3 ------ 75
20 ----------- 1 ------ 150
Parameters:
COUNT
The number of records (count) in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
VALUE
The sum of the FEE values in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
The answer should come out like this:
count = 2
value = 250
Can anybody please help me to construct the SQL statement?
Thanks in advance!
View 4 Replies
View Related
Sep 13, 2007
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
View 4 Replies
View Related
Dec 16, 2014
I am trying to construct a query with a number of DCounts for a summary report, and each of those counts is to have more than one criteria.So far, the code below is an example of the code, which returns #Error in five rows
Code:
DCount("[ID]","Attendance","[Absent]= True & " And [Date]=" Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]")
I have a feeling that it is the criteria for the date that I have got wrong, but I cannot see the wood for the trees at the moment.If it makes any difference, all of the DCounts will refer to one table and will all be within the same date range.
View 11 Replies
View Related
Jan 25, 2008
I have a form that has three different pull down menus. When I click a button on the form it opens a second form that is filtered by a query. In order to allow the user to leave a pull down menu blank the query has 6 different columns. Three are headed with the field name and three are headed with the form reference, under the form reference it either says Is Null or Is Not Null. I have a total of 8 options in order to cover all possible scenarios. I would like to be able to add more search parameters but my query is getting ridicules. :confused: Is there a way to build the same thing with a code?
Thanks!
View 1 Replies
View Related
Dec 31, 2013
I have a query that selects records based on certain flags in each record. I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form. Is there any way to automate this process?
View 14 Replies
View Related
Jul 11, 2014
I am trying to print selected query how can I do that what is the command for that ...
View 1 Replies
View Related
Jul 21, 2015
I am trying to make a query that counts the orders on a day of an specific month selected from a form.
The problem is that i want to show a chart with de 31 days of the month, even if in those days there are no orders.
Any way of making a query that adds the days with no records with a count value of 0?
View 3 Replies
View Related
Feb 10, 2006
Hey guys, my problem is this:
I want to run a query that runs off of two list boxes on a form. However, I want both these parameters to filter a single field in my query. Why I want to do this is I have several types of accessories made by several manufacturers, both included in the "Description" field. I don't want to include an entirely new field for manufacturer because it's only 4 out of about 5 items that need this.
Is there a way this can be done or will I be forced to include a manufacturer's field.
Thanks in advance
Nathan
View 3 Replies
View Related
Mar 14, 2008
I have a database with 96 tables.
8 tables per month
one query pulls the desired information.
My question is this, currently the query looks to all the January Tables.
Can I configure one query to request user input(month) and then the query looks to the tables based on the users input(month).
View 4 Replies
View Related
Mar 24, 2013
I have this Access file with Tables & Queries.
I want selected queries to appear in RED colour and if possible BOLD too.
Possible ?
View 2 Replies
View Related
Nov 27, 2013
I want to limit the results in a query to the selected items on a list box - how do I do this?
View 4 Replies
View Related
Oct 8, 2014
Is it possible to have the user select which field is added to the query through a parameter? In my query I have a table where each record has a part number and has sales number for each month. When the user runs the query I want them to be able to select which month is returned.
My table basically looks like this:
PartNumber,Jan,Feb,Mar,Apr,May etc.
01PartNumber,15,20,30,25,28 etc.
02PartNumber,12,14,12,16,20 etc
I want the user to enter "Mar" when prompted and return the parts numbers with values from "Mar".
Access 2007
View 10 Replies
View Related
Mar 27, 2014
I have just upgraded from Access 2003 to 2010. Now I'm trying to relearn some of the small things I used to be able to. In 2003, I could just have a table highlighted and select "Insert, Query". It would then open a query design with that table. Is this possible to do this in 2010? Or do you have to open query design then add the table manually?
View 2 Replies
View Related
Dec 9, 2013
I'm new to Access. We have a database that was created years ago and has been working fine. Now suddendly we get the following error message on a query.
"The number of columns in the two selected tables or queries of a union query do not match"
This is the code
SELECT [TimeSheets All].[Job Number], [TimeSheets All].Date, [TimeSheets All].Details, [TimeSheets All].[Start Time], [TimeSheets All].[End Time], [TimeSheets All].[Unbillable hours], [TimeSheets All].Who, *
FROM [TimeSheets All]
WHERE ((([TimeSheets All].[Job Number]) Like [Forms]![Search]![Job Number]))
ORDER BY [TimeSheets All].[Job Number], [TimeSheets All].Date;
View 3 Replies
View Related
May 23, 2013
I'm trying to create a query that supplies a form with data. I want to pass a TempVar to the query that is selected a from previously opened form.The TempVar is setting correctly and I can see if this if I place a textbox (NewCID) on the form showing the TempVar. The problem I have is displaying on the records according to that TempVar. If I set the query manually, i.e. "|Test|" then records are displayed but if I use the TempVar, which also displays |Test| then no records are brought back.
think it's something do with the vertical bar and that fact the field I'm searching on is a memo field, both of which I've no control over. I also have to use the Like statement because of this.Here's the query that works...
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like "|Test|"
and the one that I want to use, that doesn't...
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like [TempVars]![tmpvarCID]
I've even tried referring to the textbox instead of the TempVar, i.e.
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like [Forms]![AssetsCID]![NewCID]
but that doesn't work either.
View 2 Replies
View Related
May 17, 2013
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
View 1 Replies
View Related
Apr 13, 2007
I'm working in the petro-chem and industrial service industries now, and am finding there are relationships I haven't had much exposure to in the past.
Can someone please help me with constructing the tables and relationships needed here?
This is something I should probably know, yet I've never created junction tables which must take into account nested data. I have created a relationship which works, but I feel it could be done in a simpler fashion.
By the way, I cannot find Pat Hartman's many-to-many sample db. It is either missing, or the restrictions on word length in Search just aren't letting me find it.
Table: WorkOrder is the primary table I'm working with. I need to store 4
pieces of information for every WorkOrderID, two of which have subs.
Here's a picture of the data I need to collect:
View 4 Replies
View Related
Sep 10, 2013
I am trying to set the following variable that will be used to create a PDF file in an already existing folder and name the file WorkorderIDddmmyyyy-hhnnss.pdf
The following variable setting creates the filenameddmmyyyy-hhnnssWorkorderID.pdf.
mFilename = "C:RPR AccessPDF Reports Emailed" & Format(Now(), "ddmmyyyy-hhnnss") & WorkorderID & ".pdf"
DoCmd.OutputTo acOutputReport, mReportName, acFormatPDF, mFilename, True
I just can't figure out how to construct it correctly.
Also, is there a way to dynamically create a folder if the folder doesn't already exist? I currently hard code the folder name but would really like to create the folder name based upon some other variable.
View 8 Replies
View Related
Nov 16, 2006
Hi,
I am trying to creat a DB that can potentially recieve input from a serial port which has a unique ID on it and updates the corresponding feild within the DB. I don't need to demonstrate full functionality (although if I have time I might give it a go!).
What I want to show is that the DB can accept a unique ID from this external source. The ID will just be to identify a user and a peice of equipment, potentially the feilds in the table would be updated with information like, time of session, duration, speed. I am assuming I may need to use VB to do some programming and create an incoming event. Just wondering what the most logical solution is. I want to bring up an interface when an input is recieved, like a user interface or pop up which says eg. "Hello Jim". Then it is possible to demonstrate that they have been detected.
Any help would be hugely appreciated!
Thanks,
Minxka
View 5 Replies
View Related
Jun 12, 2007
Hi everyone,
I am fairly new to Access, and need some help with what I thought would be an easy project. I have an Acceess table that contains data on settings for machines in my plant. There are columns for part #, Machine #, part type, etc. All in all there are about 20 different columns. Now what I need to do is have a form where a user can enter one or two fields, and print a report that contains all the information from all 20 columns.
For example, the user would enter Part number "AB1234" on machine "15-1" and would get the following data from the table:
Part # part code Equip. Temp height etc
AB1234 A 15-1 100 5.3 ...
AB1234 B 15-1 200 6.3 ...
AB1234 C 15-1 250 7.3 ...
They could then somehow choose exactly which record they wanted (example : ABC1234 B) and print a report that contained just the data from that selection.
I hope I explained this clearly enough. Basically I want a user to be able to enter 1 or 2 selection fields and then be able to print a report that contains info from 1 row in 1 table. Any help would be appreciated. I am somewhat new to this and havee a basic understanding of Access and A moderate understanding of VB. Thank you in advance for your help.
View 1 Replies
View Related
Jan 7, 2015
I'm running a query that has a field where I need two criteria show up (regular collections and special collections). Another field I have selects for the date (10/1/2014). The last field is the sum of collections. My problem is that the query is generating regular collections and special collections with each date and a sum of collections. I need the total sum of both collections for the date which would be 4.
AGENCY_CODELINE_ITEMPCA_DATESumOfCOLLECTIONS/ACCOUNTSxxxRegular Collections10/1/20142xxxxspecial collections10/1/20142
View 2 Replies
View Related
Aug 29, 2005
Howdy. I have a question on design of tables. Most of the databases I have seen developed here (and in books) assume that there is some kind of individual input of data. Thus, one major table for input (of various sources) worked well on my other projects. This one doesn't seem to fit the mold.
I have Excel/CSV files from five different vendors. Each deals with a different kind of data. That is, while all of them will have a few common elements, there are several significant differences in the other fields that I cannot bring them into the same table. For instance, in one table one field might be "Size" and refer to inches, while another table would have size, but it refers to the portion of the page (1/4, 1/3, etc.). Thus each of the five vendor tables have unique fields.
Tables:
Input tables:
tblTV
tblNPP
tblRAD
tblOOH
tblONL
Ref Tables:
tblSEGMENT
tblMEDIATYPE
tblDMA
tblDAYPART
For instance, fields for tblTV
TVID
Station
DMA
Daypart
Program
Length
DateStart
DateEnd
TRP
NumberSpots
Cost
MediaType
Cons/Bus
CreativeDescript
CreativeOffer
Segment
So, my questions:
1. Is it acceptable to have five input tables, rather than one?
2. These five vendors will update their input data on a daily weekly basis. This, there will be changes to some fields and addition of many new records. What is the best way to handle this?
3. In addition, we will have 2-5 people updating several fields (i.e. last five in tblTV above) that the vendors cannot supply. Will forms be the easiest way to accomplish these updates?
3. Relationships will be critical in getting this set up correctly. Any suggestions?
Any kind of direction, suggestions will be very much appreciated.
View 8 Replies
View Related
Mar 23, 2005
I have a form that contains 5 fields and want to ensure that duplicate information is not entered. I am aware that when creating the form you can set Duplicate Records to No but each record has an AutoID which I assume means that even if the information is the same it will not register as a duplicate record due to the different AutoID.
There should only ever be 5 records entered per day. One for each region
I have created a Sanity Check that is displayed as a sub form that shows if the relevant information has been entered for a given day in Bookings and Backlog but this doesn't stop the user from putting in the same information twice. i.e. the User starts inputting the information gets called away and then trys to put it in again but doesn't check the sub form.
The Table in which the information is stored is called Bookings and Backlog.
The 5 fields on the form which is called Bookings and backlog are
ID (Autonumber)
Date (Short Date)
Combo26 (Text) (5 Regions:- North, South, East, West, Central)
Bookings (Number)
Backlog (Number)
I have put the following statement in AfterUpdate on the Combo26 field
If Me.Date = DLookup("[Bookings and backlog]![Date]", "[Bookings and backlog]", "[Bookings and backlog]![Date]=[Date]") And Me.Region = DLookup("[Bookings and backlog]![Region]", "[Bookings and backlog]", "[Bookings and backlog]![Region]=[Combo26]") Then
'Define Message Box
MsgBox "The Region you have chosen already has data entered for this day." & vbCrLf & vbCrLf & "Please check your records and amend were neccessary.", vbCritical, "USER INPUT ERROR"
Else
End If
However I'm not to hot with writing VBA and probably got something wrong somewhere as it doesn't seem to do anything and lets the user progress regardless. I have tried amending the above script and asked it to look at just one field but this just seems to give the message regardless of the information selected in the Combo box.
Can someone give me an indication as to what I have done wrong so I can put it right and in a language that is easy to understand so that if I have a similar issue later on I don't need to post another question.
View 3 Replies
View Related
