Access Queries With Multiple Yes/no Fields
Nov 7, 2004
I have created a table with multiple Yes/No fields. I wish to create a
query where the user can select from a form that displays all the Yes/No fields
Is it possible for this to be done
Thanks for your help
REFINEMENT OF PROBLEM
I have created a table for a small library at work. The items in the table are item_number, author, title etc. Also I wanted to classify each item into various categories. In the Table I have set the categories as Yes/No fields. An item can belong to a number of categories eg admin, finance,resources etc. What I would like to do is for a user to be able to interegrate the table via a query to find the articles that meet the criteria they want to search on. Ideally this would be done via a form that the user can tick the applicable categories (Yes/No fields). I tried setting up as parameter queries however this is very messy. Is there a way it can be done. Sorry for confusion in first posting.
View Replies
ADVERTISEMENT
Dec 2, 2014
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
View 2 Replies
View Related
Nov 15, 2014
I have a DB that consists of Movie titles and the multiple dates ( as many as 10) on which they will be used in the coming year.I built a flat table with 10 date fields.Then tried to build a relational table with just movie title and dates linked to ID.I cannot work out an ability to SEARCH the Database for a SPECIFIC DATE and get returned a listing of ALL Movie titles that will air on that date,
View 9 Replies
View Related
May 20, 2013
Is there a way to have one parameter in a query effect multiple fields?I have a table that details service calls on our equipment. There are columns in that table for each part of the equipment that get marked, depending on what needed serviced (i.e. software, printer, keypad, monitor, etc). I would like to be able to run a query that will show all the service calls where one part of the equipment was serviced multiple times without have to enter multiple parameters or write a union query (there would need to be 11 unions or 11 parameters for each part to be captured). For example, I would want to know how many pieces of equipment had 3 or more service calls on their software. I want to run the query without having to say that I don't care how many service calls were performed on the printer, monitor, or keypad. I would like to be able to say "Software >3" and have the query run on that information.
View 1 Replies
View Related
Oct 22, 2013
I'm trying to build a query that uses the information pulled from controls on a form called "CharacterCreation" - 2 of the controls are "Race" and "Class", and the form stores this data in a "Characters" table.The query needs to refer to the Characters table, and another table called "Modifiers".In this second table I have several numerical fields such as hp, str, etc, and I have two other fields called "Type_Modifier" and "Type" Among the Type_Modifiers are Race and Class, where their corresponding "Type" could be for instance Demon and Magician..
So the idea is that when I mark on the form (and thus creating the record in the first table) a Race of "Demon", and a class of "Magician" I want the query to refer to both of these fields, and sum the values in the second table where the "Type" is either "Demon" or "Magician" or whatever I decided to choose. I feel like I should be able to figure this out but I'm having a hard time..Here's an example of the SQL I've tried that shows nothing.
Code:
SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp
FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race)
GROUP BY Characters.Char_Name, Modifiers.Type
HAVING (((Modifiers.Type)=[Characters].[Race]));
I also tried this, which I thought would work but it displays nothing as well.
Code:
SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp
FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race)
GROUP BY Characters.Char_Name, [Type]=[Characters].[Race];
View 12 Replies
View Related
Jun 5, 2013
I have a query that based on certain codes in a record does multiple joins to pull in other codes... I want the query (based on these pulled in codes) to do a lookup in another table to extract 1 field that has a description...
Can I do this in one query??? Do I need to do a Make Table to first pull in the codes and then do a second query with a DLookup based on those fields???
View 3 Replies
View Related
Apr 26, 2013
I have a table, which is being populated by a number of users. The table has 211 rows (customer field - this is a fixed amount) and there are 5 other columns (information which might or might not be available for that customer) which are all yes/no fields. Each row may could have any combination of ticks in those 5 columns.
I have a form to fill out this information, but now I need to use the data.
I'm trying to create one query which counts how many of each field are populated. I need it to give me the total of each of the "information" columns, and another to give me a list of all of the customers which have at least one of the information columns ticked.
I will also need to be able to see how many customers have all of the information columns ticked, and how many have none.
View 1 Replies
View Related
Sep 16, 2014
lets say I have a table with five tasks for five employees and each employee's data type is with 'Yes or No'.
I need to have a desired result to check
1.if all the employees have completed that particular task then the result should be 'YES'
2.if anyone employee has not completed that particular task then the result should be 'NO'
so, for each task there must be a check on all the five employee's data type.
View 1 Replies
View Related
May 15, 2015
I have a table which has each claim as a single record. A client wishes us to provide the data in a format that lists the each procedure on its own line. I have attached a highlevel example of what the data currently looks like and what the query results should look like.So I need to create a query that would repeat the Claim Number and place all the Procedure fields into one field.
View 1 Replies
View Related
Jun 26, 2013
I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.
OTHCPT2OTHCPT3OTHCPT4OTHCPT5OTHCPT6OTHCPT7
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 #NULL!#NULL!
2318095978C176788307J0131 J2270
For instance if 23180 and J2270 are in the same record I want to identify them for all of the records.
View 7 Replies
View Related
Sep 5, 2014
I have the following source data:
Code:
Item Revision Start End
332 B 9/2/11 9/15/11
332 C 9/21/11 9/22/11
332 E 11/2/12 11/29/12
2A9 A 1/13/13 1/14/13
2A9 C 1/16/13 1/18/13
I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):
Code:
Item Revision Span
332 C 6
332 E 407
2A9 C 2
I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?
View 4 Replies
View Related
Aug 26, 2013
I am trying to set up a calculated field in one of my form querries, using expression builder.
In one of my source tables I have 4 date fields called Inspection date 1 to 4
I need the lowest date among those inspectinos which are in the future (next inspection) If all 4 dates are in the past or Nulls, the function should return the current date. The logic of the expression could be:
MIN(MAX(D1,Date()),MAX(D2,Date()),MAX(D3,Date()),M AX(D4,Date()))
How can I do this in expression builder?
The built-in functions DMin and Dmax work with single field arrays, witch would be perfect if the database were properly designed, but now I dont have the power to change this.
View 1 Replies
View Related
Oct 15, 2013
I have a problem that I can't seem to solve in SQL for my access 2010 query.
Let's say I have a the price of and for every year.
In the tables I have A building number, a building type, and electricity and water predictions for this year and many upcoming years (up to 40 years)
I need to apply a change to any building with the building type BRT to show only 10 percent of the electricity and water for ALL years (up to year 2052). So building 5 would show 8.5, 5.4, 9.5, 7.4, and so on.
View 2 Replies
View Related
May 8, 2013
I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
View 2 Replies
View Related
Feb 21, 2015
I have never created a single query that is this complicated.
I have a time card database that I created back in 2003 and over the years more and more new task have been added to track volunteer hours has been incorporated into it.
The current task at hand is to determine if each member has achieved or meet certain requirements each year.
They are attend 8 or more meeting and check into 5 or more nets and be Net Control Operator for at least 1 radio net and be involved with 1 or more Public Service events. Sounds simple right?
I have a table that contains all the information that is need to get the result I want but am having a problem getting that result with a single SQL query. Everyone you talk to says use SQL query to obtain calculated vales and never store/save them.
So from the work table below meetings consist of 4,5,6,8,9 and nets consist of 10,11,14,15 and events consist of 2, 13 and the NCOflag is T/F.
So those are all the parts, count the number of meetings, nets, events and NCO and set the meets requirement flag T/F
I keep getting "You tried to execute a query that does not include the in specified expression <name> as part of an aggregate function. (Error 3122)"
In this case it keeps complaining Callsign.
You did not enter an aggregate function in the TRANSFORM statement.
Here's the statement I created:
SELECT [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].NCOflag,[Work Hours].SubMtg, [Work Hours].SubNet, [Work Hours].SubNCO, [Work Hours].SubEvnt, [Work Hours].ReqSubTotal
FROM [Work Hours]
WHERE (((Year([Date Worked]))=(Year(Date()))))
[Code] .....
It is failing on the first HAVING (([Work Hours].SubMtg) =Sum(). I have tried many variations and changes but don't see what is wrong with it.
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
Nov 24, 2013
I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.
View 3 Replies
View Related
Aug 12, 2015
How can i update fields of one table with update query where creteria are more than one 1
Example:
DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"
What is the syntax error ...
View 5 Replies
View Related
Aug 14, 2013
i essentially have 2 table:
1. Table BIC
A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.
2. Table Original
A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.
** note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.
What i need to do is create a query that will:
1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".
2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.
3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.
View 4 Replies
View Related
May 13, 2014
I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
View 7 Replies
View Related
May 15, 2013
I am trying to find the best way to compare estimated expenses to actual expenses in Access. I am working with vessel and port call information, so each port call has a series of costs associated with it. I have two tables, each with multiple fields (around 100). Each of the fields contains a cost type that goes to a particular cost code. Each row of the table is associated with one particular port call. The call is estimated right after it happens, but the invoices are not all recieved for up to three months. Once all of the invoices for this port call have been recieved, we enter the actual expenses for the call. I am trying to find the best way to analyze the costs through finding the variances for one particular port call for each code, and also for multiple port calls to see which cost item has the largest/smallest variance for further investigation into how to estimate it more accurlately. I am thinking that I may have to move into Excel to do this, but would like to stay in Access if possible.
View 4 Replies
View Related
Feb 17, 2015
I am trying to do is to make a for loop to insert multiple text fields in on table.
Depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that I'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.
Code:
For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr)
SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _
"KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _
[Code] ....
View 3 Replies
View Related
May 30, 2015
I'm creating a query from one table using two fields that require certain records to NOT bring up records that have the following text:
One table, two fields
First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"
Second field ECR LifeCycle Status Criteria is Not "COMPLETE"
When I put the Not Criteria in the first field only I get a result.
When I put the Not Criteria in both fields I get no results.
View 9 Replies
View Related
Nov 9, 2014
I have a database that contains details of payments returned to a bank account without sufficient information to identify who the money belongs to.
The DB is used by a team of people to retrieve details of an individual entry and when they have figured out who the money should be sent to they can mark that the payment has been "located". We can only send the refund at certain times of the month, so there is another activity "applied" where we have entered details on internal systems for the refund to be processed. Finally, once a month we physically transfer the money out of the bank account and add a "completed" date to all entries that were "applied" before that date.
My manager would like to be able to look back at a certain date to see what the balance was so he can make charts and whatever else in Excel. The balance will be all payments that were not marked as "completed" on that particular date.
I'm having trouble making a query that will give him the balance for each day in the past.
The important tables/fields here are
TBL_MAIN - dateReceived (date) and amountRefunded (currency)
TLK_LOCATED - appliedDate (date/Time) completedDate (date/Time)
TBL_DATES - activityDate - just a list of dates, which I use for a similar query of the amount we located/applied each day
So I'm looking for a query that will list all dates in tbl_dates against the sum of amountReceived up until that date, with the sum of amountReceived where completedDate is less than the date listed subtracted.
View 4 Replies
View Related
Oct 4, 2007
Hi,I have a table which contains sales information like deal name,closing date of deal,Monthly revenue information,total revenue yearly also quarterwise
information.The below table gives some idea....
i want to enter data in multiple fileds ex:when I enter revenue amount it should get updated in corresponding month based on the closing date information.Suppose closing date for a particular deal is mar-08 then the total revenue entered in revenue field should get updated in march month field.Please help me how to edit data in multiple field and also logic to apply to solve my problem.I have to design a report based on this information and source should be one table so i want to edit data in main table.
Any help would be appreciated...
I am new to ms access database so got stuck..pls help me
Closing date Jan Feb ar Apr May Jun Revenue Q1 Q2 Q3 Q4
Mar-08 1,500 1,500 375 0 0 0
Jun-08 4,000 4,000 0 0 0 0
Jun-08 6,000 6,000 0 4,500 0 0
Jun-08 3,000 3,000 0 0 0 0
Jun-08 1,720 1,720 0 0 0 0
Jun-08 20,000 20,000 0 0 0 0
Jun-08 2,000 2,000 0 0 0 0
Jun-08 20,000 20,000 0 0 0 0
Thanks in advance. yuor help is appreciated,
Regards,
kala
View 2 Replies
View Related
Mar 23, 2015
My colleague has a multiple table database and uses lookups for some fields. The simple query was to extract 3 fields and display all records from those fields. The result of the query was that the selected fields were listed first followed by all other fields which are usually not displayed.
View 5 Replies
View Related
