Multiple Parameters [for The Same Field] For A Single Query?
Nov 23, 2005
I have set up a parameter query in Access 2003 that asks the user for the "Section", such as "Admin", "Accounting" etc.
I need them to be able to respond to the prompt with more than one section if they want- sometimes just one, sometimes two or three or four.
So they can get "Accounting" and "Admin" both in the records that are returned.
The code:
SELECT T_ElainesMaster.Section, T_ElainesMaster.Login, T_ElainesMaster.Workstation, T_ElainesMaster.NT, T_ElainesMaster.Barcode, T_ElainesMaster.[PC model], T_ElainesMaster.[Emp Name], T_ElainesMaster.[swap or not], T_ElainesMaster.[Exp Date]
FROM T_ElainesMaster
WHERE (((T_ElainesMaster.Section) Like [What section would you like to include?]))
ORDER BY T_ElainesMaster.Section;
Thanks!
View Replies
ADVERTISEMENT
Oct 21, 2004
I'm pretty new to Access, so if I'm doing this the hard way, that's why.
I work in a quality control position, and I'm trying to set up a single Access database for the QC staff to use instead of everyone having their own seperate Excel workbooks.
The issue I'm running into at the moment is on a report. Each record is graded on four seperate types of criteria, Error Type 1, Error Type 2, Error Type 3 and Error Type 4, all of which need to be reported on seperately. So I have at least four queries set up, all with the same parameters (right now, just review date.) I'm trying to pull through all four queries on the same report, and so far I've had success having the report ask for the parameters only once and then applying it to all four queries. However, I'm running into a problem where Access is now forcing the filters of each individual query on to each of the other three queries, so it's only pulling through records for all four queries that match the criteria of all four. Any record which only matches the criteria of one, two or three of the queries is being left off the report.
Sorry if this sounds confusing. Anyone have an idea as to what I'm doing wrong?
View 3 Replies
View Related
Apr 30, 2014
I am trying to find a way to allow the user to enter multiple numbers in an unbound box that I will pass to a query as parameters. I already have it set up to pas the parameters from the form but if i try to do more than one number it doesn't work.
I have tried:
1306 or 1307 or 1308
1306, 1307, 1308
"1306 or 1307 or 1308"
I was reading somewhere that when the value is picked up from the form it is not like you are typing it right in the criteria box of the query.
So are there any other options here? Is there a way to enter all the number in a box and then use vba to create an array and then pass that to the query?
View 2 Replies
View Related
Feb 12, 2014
I am designing a database in Access 2010 to handle customer discounts. The problem I am facing is that the discount can vary by customer. One could be a straight percentage based on certain sales totals, even the percentages can vary by customer, and some customer discounts are tiered. I was hoping there was a way to handle all through one query, because there are so many variations, it would require several queries if I separate by each calculation available.
Is there a way to set up a table listing the customer and their corresponding calculation expression then running a query using that table and the sales data table to do all the calculations. Or is there another way to handle this level of complexity in Access? The end result I would like is one data set with each customer and their calculated discount.
View 1 Replies
View Related
May 11, 2014
I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.
Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));
What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.
But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.
Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.
View 7 Replies
View Related
Feb 8, 2015
I'm creating a database for my wife to use in her work, one of the fields is dates visited and the user should enter "00/00/0000, 11/11/1111, 22/22/2222, 33/33/3333" in this format. The field is a large text format because there could be anything from 1 date to hundreds. What I need to be able to do is in my query I need to enter 2 dates and have access return a how many dates exist between those 2 date values. eg It will pop up a box asking for start date and then another asking for end date and then it has to return the count total of how many dates exist between those two date values. I don't know VB and have had only basic training in Java and C# none of which involved Access. The only way I can think of doing it is to have access count the number of commas within the date field required but I don't know the formula required assuming it can even be done.
View 1 Replies
View Related
Nov 12, 2004
Hi, this is my first post here.
My question is this: I am creating a database for a student survey. Some of the questions may have multiple answers.
For instance, question 2 reads:
"What location/time are you taking courses (select all that apply)"
and the possible answers are:
1)Daytime/Oakland
2)Evening/Oakland
3)Saturday College
4)BC
- Where a student could be taking classes at multiple locations or times. I am completely lost as to how to set this up. I have tried to set the properties for the field on the form to accept multiple responses, but of course, these do not save to the table and so it's completely useless.
Thank you in advance for your help!!!
View 6 Replies
View Related
May 20, 2013
I have question regarding multiple choices fields. I would like to store constrained but multiple values in a single field. I used the assistant to create the multiple choice field in the table, but I'd like to have it displayed in my form with check boxes (more readable in my tabular form) instead of drop down list. Is it possible ...
View 6 Replies
View Related
Jan 4, 2006
I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...
strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"
Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.
SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;
Your help will be greatly appericated.
View 3 Replies
View Related
Nov 17, 2006
I have a table of every reading of every electric meter. This history goes back a couple of years.
Field1 = date
Field2 = Meter#
Field3 = Single digit code identifying method of reading
The same meter is read multiple times in a year. The number of readings in the database is 1 or more (no limit).
I want to create a single line of text in 1 field that shows a history of the reading method of a particular meter.
Reading method translations
A=Radio Frequency
B=Manual
C=Not Read
X=Estimated
Here's a sample of what I would like to see .....
View 4 Replies
View Related
Apr 5, 2007
I have a query that displays all records. I need it to limit based on multiple criteria in a single field. I.E... instead of all employees from every section, I just want it to display employees from section A, C, D, F one time and next time maybe go with section B, C, D. For some reason the solution eludes my little brain.
View 3 Replies
View Related
Oct 3, 2006
I hope someone can help me on my problem below:-
A user wanted to key in the data in a single form where she can select where
a particular procedure is located. The procedure could be duplicated and
placed in a few departments.
My tables are as follows:-
tbl_Proc_Dept:-
ProcNo - Text (Primary)
TrgConducted - Yes/No
DeptAbbv - Text (Primary)
tbl_Dept:-
DeptAbbv:- Text (Primary)
DeptDetails:- Text
I created a form where i have the following fields in the form:-
1. ProcedureNo - Text
2. TrgConducted - CheckBox
3. MainDept- ComboBox (DeptAbbv data field)
4. Finance CheckBox (DeptAbbv data field?)
5. Admin CheckBox (DeptAbbv data field?)
6. Purchasing CheckBox (DeptAbbv data field?)
7. Facilities CheckBox (DeptAbbv data field?)
8. MIS CheckBox (DeptAbbv data field?)
How can I add multiple deptabbv field in a single form?
Pls help.
Thanks.
View 1 Replies
View Related
Dec 28, 2006
Hi, this question is more about Microsoft Access than it is about ASP. However it involves a database that I am setting up for a website being written in ASP.
I have a table which is going to store business details. Every business belongs to a business category and related subcategory. For example Garages and MOT Centre.
I want to store up to 5 categories for each business. Is it possible to link to the categories and subcategories tables for each of the 5 category fields in the business table?
Thanks, Steve (Blackpool)
View 1 Replies
View Related
Feb 14, 2012
I am facing an issue with an access table.I have a field named "Contact Dates" , supposedly that field will store in a drop down list all the dates that the contact with the related company was made, how can I store it ? I am using in the current moment a memo data type to store the data and a comma for splitting them. How to create a drop down list for storing and displaying all the recorded dates and recording all the new data values as date format.
View 3 Replies
View Related
Oct 31, 2005
I am trying to run the below union query, but it keeps asking for FRGHT_BL!FB_CREAT_DTM as a parameter. I want it to only ask for the Start Date and End Date once.
SELECT * FROM qLOC_ID WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION SELECT * FROM qLOC_ID2 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date])) UNION Select * From qLOC_ID3 WHERE(((FRGHT_BL!FB_CREAT_DTM) Between [Enter Start Date] And [Enter End Date]));
View 1 Replies
View Related
Nov 6, 2014
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee
Field: Name
Form: frmUpdateEmployees
Queries: 16 that depend on the table and field above.
View 5 Replies
View Related
May 7, 2013
I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?
I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.
View 2 Replies
View Related
Apr 21, 2013
I have a table in which there are approximately 15k records. The name field has approximately 2500 unique values. I need to search the table for multiple names and return the results from the table. This would then be used as a report. Due to the users being very novice, a form is needed for the search.
Example:
Search for Smith, John; Wilson, Bill; Jones, Jeff
Return
Name Year Brand Make
Smith, John 1999 Oldsmobile 88
Smith, John 2001 Chrysler 300s
Wilson, Bill 1994 Porsche Carrera
Wilson, Bill 2007 Kia Sporty
Jones, Jeff 2004 Chevrolet Camaro
Jones, Jeff 2011 Chevrolet Impala
My knowledge of forms is basic, however, I can be a quick learner if on the right track.
View 1 Replies
View Related
Apr 6, 2014
I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.
tblPersons
PersonName PersonDate PersonShift
Jason 4/10/14 FIRST
Jason 4/13/14 FIRST
Jason 4/13/14 SECOND
So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.
The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?
If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?
I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.
View 2 Replies
View Related
Aug 5, 2012
Add and Index named Location to the tblGuest table that consists of the StateProv and City fields and uses the default property settings, Delete the PostalCode Index, save then close the table.
I have looked up all info in the text reffering to Indexing and cant seem to come to a conclusion of the way to complete the underlined area area. For the class they provide you with the files and in this particular file the primekey is Guest ID with a field value of GuestID. There are two other Indexes PostalCode with field value PostalCode and GuestID with field value of GuestID.
View 2 Replies
View Related
Mar 30, 2006
Hello
I have created a database for my department to log all of our jobs to keep track of them and I want to create a simple search for them but I am having difficulties.
I am familiar with creating queries to search tables for matching records, but is it possible to get search criteria from the user (ideally from a search form they fill in) to form the query?
For example, I would like the user to be able to query the jobs by month and/or business area and/or supplier...is this possible?:confused:
Any help gratefully received!!!
:)
View 2 Replies
View Related
Jul 3, 2013
I have a query thats outputs my costs on a project divided into 50 categories. Additionally, each project can be divided into 3 stages, each with the same 50 cost categories. I have been using a query that prompts the user which stage they would like to look at, and it works great.
However, it would be useful to be able to see all stages at once, next to one another.
So, essentially, i want to be able to input the stage parameter in the criteria box for three fields in the query, so instead of showing the costs for one stage at a time, it will show all three(and a summation across all stages), without a prompt.
It works when i set the one field manually to the first stage, but when i add additional fields, it returns a blank query.
View 1 Replies
View Related
Nov 4, 2013
I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.
Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents
Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents
Function:
Option Compare Database
Option Explicit
Public Function ConcatField(strSQL As String) As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String
[Code] ....
View 2 Replies
View Related
Aug 15, 2013
Im constructing a form with check boxes next to names. Id like to have the checked names input serially into a single table field like:
IssuedTo
Name1, Name2, Name3
There are 8 possible Names. Is there way to do this without a long complicated If-Else-Then function?
View 5 Replies
View Related
Nov 9, 2007
Hi,
I really hope someone will be able to help me with this one as I am sure im just missing something simple.
I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:
Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])
Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have yes in either field. Currently, if more than field is ticked the query treats it like:
Field1 And Field2 And Field3 And etc = true
I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.
Thank you
View 13 Replies
View Related
Oct 18, 2007
Hi, thanks in advance for any help you can offer. I've got a table that has
Date
Time
Tag ID
Power Level
throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day
I've tried using "group by" and max in the query but this gives me all the times throughout the day.
anyway, thanks again for looking
cheers, Shaun
View 2 Replies
View Related