Queries :: Create Parameter Query To Select Which Column To Calculate Weekly License Info
Mar 2, 2014
Basically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.
Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").
Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want
As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.
For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.
Right now the queries are all configured as the "Select" type.
I have a database of around 15,000 users and I'd like to create a query that I can run on a weekly basis and save the results to an Excel spreadsheet. The results need to be logical and understandable by my coworkers.
Unfortunately, the actual results of the query are not (in their raw form) logical or easy to interpret.
Let's say I have a table called "users" and within that I have:
Surname Forename FieldA FieldB FieldC
FieldA has a value of either NULL or a 12-digit number FieldB has the values are "ENABLED", "DISABLED" and "N/A" FieldC contains a value of either "1" or NULL
This means nothing to my coworkers who want each user to be sorted into a "category". As I'm running this on a weekly basis, I'd like this query to do the work for me, so I don't have to manually assign everyone to a category in Excel. Plus, of course, there is no chance of human error if the query does this for me.
Sooo... I'd like my query to categorise for me as follows:
Category1 = FieldA IS NOT NULL and FieldB="ENABLED" Category2 = FieldA IS NOT NULL and FieldB="N/A" Category3 = FieldA IS NULL and FieldB="ENABLED" Category4 = FieldA IS NULL and FieldB="N/A" Category5 = FieldA IS NOT NULL and FieldC = 1 ... etc.
I'd like the final column in the query results to simply list the category name, so I can simply copy and paste the data into an Excel spreadsheet and be done with it, safe in the knowledge that it makes sense to all.
tell me if it is possible to calculate a value in a column in a datasheet view of a query. i.e. I want to take a value in 'Column A' and subtract the value in 'Column B' to create a value in 'Column C'?
I'm creating a query for a someone who wants quantities summed by a weekly range and correspond to a week number. This person wants Access to do something that is a simple function on Excel.
I have no clue how to do this or if Access is even made to do something like this.
SUMMARY: In Access 2007, can I pass a Column Name as a parameter from a Combo Box in a form?
DETAILS: I would like to use a String value from a Combo Box as a parameter in a Inner Join query: ... WHERE (((AAA.HSC) Like Forms!My_Form!My_TextBox) AND((CCC.Forms!My_Form!My_ComboBox)="X"))
Where "CCC is a Table from my Join Query
I'm able to run the code above without generating any errors. However, instead of the query accepting the value from the Combo Box, it opens a pop up asking for the value of "CCC.Forms!My_Form!My_ComboBox", not once, but twice! After it finally runs, the result set is empty when it should not be. I also find it strange that it is asking for the combo box value with the Table name appended to the front.
I have form with a button on it that launches a parameter-based Select query (which served as the source for a report). I didn't have any validation measure in place, so if the User supplied a bogus value, a blank report was generated. While not technically an error, it would seem more polished to generate a warning message if the User supplies a bad query value and prompt them to re-enter.
Having read other posts along these lines, I've added an unbound text box to the form which the User fills in first before clicking the button. When the button is clicked, it executes code that uses the DCount function to make sure the text box value is in the source table, and if it is then it runs the parameter-based Select query. My question is how to pass the value in the text box to the query as a parameter. Below is a sanitized version of the code that I've generated so far.
Private Sub SingleItemRptB_Click() If DCount("[FieldName]", "[Table]", "[FieldName]=[TextBoxValue]") = 0 Then MsgBox "Item not in database. Please check value and re-enter." Else DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly End If End Sub
I created a form that has 3 multiple drop-down selections that is supposed to select records from [TableName_1] based on those selections. I initially created the query using the query designer with actual "hard coded" selections to make sure it worked. When it gave me the expected records, I changed the criteria from the "hard coded" option to the input form's control reference [Forms]![FormName]![ComboName]. I did this one at a time and tested after each change.
After the first change, I got exactly what I expected.
After the second change, it seemed okay. (note: I put the criteria on the first criteria line in the query designer, not on the second line)
After the third change, I get the dreaded: The expression is typed incorrectly or is too complex to be evaluated....
Here is the SQL:
SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA FROM (SELECT M_Lending_Institution.InstitutionName, M_Lending_Institution.GeoRegionID, M_Lending_Institution.SpecialtyID, M_Lending_Institution.SBA FROM M_Lending_Institution) AS LenderSearchQuery WHERE (((LenderSearchQuery.GeoRegionID.Value)=[Forms]![LoanSearch]![CmbPrefGeo]) AND ((LenderSearchQuery.SpecialtyID)=[Forms]![LoanSearch]![CmbSpecialtyArea]) AND ((LenderSearchQuery.SBA)=[Forms]![LoanSearch]![CmbSBA]));
And once this is sorted out, yes I want to be able to allow for all records to be returned if any of those fields is NULL. I've read a LOT of posts around this subject, but I can't seem to find anything that answers my question without jumping into advanced VBA code.
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
I am trying to create a query with a parameter on it, but on a date box, but only searching for a specific month, without regards to the year. So what I want is for me to run the query, a pop up box asks "which month?", I then put in Jan for example, and the results will show all records with the month Jan regardless of the day or year.
Is this possible? If so - whats the code I would use?
Is it possible to create a query that asks for multi entries in one column .....
For example : If I have parts that I purchase and some of them get rejected I want to inform someone of those parts, these parts could be on order numbers, 1,2,8,10 ....
I cannot just search on rejected because some parts could have been reject say 2 weeks ago but new ones have not arrived yet so the flag "rejected" is still showing ....
Is it possible to set the criteria that when asked for the order number/s you could type in ,1,2,8,10 ....
In a table I have a field that gets its value from a lookup with multiple columns. Is it possible to query information in the columns rather than the actual value.
Here is an example of a single field value in the table I want to query (this would be in a drop down box in the table):
ID | Name 1 Nick 2 Nick
The actual values are the 1 and 2, but I would like to query the name 'Nick'. How would I do this?
I'm trying to create a query to work out the total amount to invoice based upon some selections. Currently my query looks at the values in several fields (numberOfRollsUsed etc) by taking the value in these fields and multiplying by a fixed amount to calculate the total.
What I would like to add to the query is fixed values based upon some check box selections. So if check box A is selected, add 5 to the total, is check box B is selected, add a further 10, and so on. All fields and check boxes are held within the same table (Job).
Am I being daft or trying to do something in the worst way possible? I'm actually adding this to an existing system so I don't have so many options to completely redesign the system to calculate this in a better way.
Below is the code that I have for a query. Currently the GrandTotal Column appears to the left of all the Weekly Columns. Is there a way I can have the GrandTotal column appear to the right of all the Weekly Columns? The weekly columns will expand or contract depending on the dates selected.
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 ); TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal FROM [Trends-1-3TON-WEEK] GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
When I attempt to use the me.field = me.combobox.column() method to retrieve info from another query,it only works if I use column 0 or 1... it doesn't work with higher columns number.
- field name: cbofi - row source: listado-unico-fi <-- it is a query with unique values and 7 fields in total - event procedure, change:
Private Sub cbofi_Change() Me.txtDOCTPT = Me.cboFI.Column(1) Me.campovia = Me.cboFI.Column(5) End Sub
- txtDOCTPT is the field that indeed works - campovia is the one that does not work
I tried using column(0) and column(1) and it worked fine... but higher column numbers do not work. Just mentioning because the field names are all right.
I am creating an absenteeism database which has the following tables:
tblEmp - PK - auto number, EmpId, First Name, Last name tblFunction - PK - FID (autonumber) has departments tblTL - PK TLID (autonumber) has list of team leaders tblRelated - pK - Id (auto), EmpId, TLID, FId as long integeres tblCodes - CodeId (auto), list of absenteeism codes like late, mia, etc EmpLeave - pk - autonumber, empid, codeid, hours, date of absenteeism
I have a Qry_Master which just joins all the information together as it gets updated on a monthly basis
Now, I am trying to create a form where the user has option to select one or more tls and one or more codes and when they hit the button, it should come up with all emps that have those codes and report to the team leader selected.
In my form, I have made both my list boxes as multiselect and i have Qry_frm that is a parameter query but when i run the button nothing happens and i cant seem to figure it out. I have attached the sample database to this thread.
If there was a way I could get the crosstab query in Access to allow more than one column I would solve a major problem. Is that possible?...help anyone?
I have created a table for a Gym that shows which classes / activities are on which days. You will notice in the image below that i have assigned Boolean to associate a class with a day of the week.
I now am trying to create a Calendar STyle report that GROUPS all activities by the day they are on. So, for example, see below:
As you can see, all of Monday's activities will appear first (in a group) followed by Tuesdays.
One way I think I could achieve this is by doing a QueryMonday, QueryTuesday...etc so I have dataset or resultsset for each day. Then I could bring into a report, each query as a sub-report. I believe this is overly complex for what i'm trying to achieve. Also, using 5 grouping levels in one report doesn't achieve it either.
I want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?
Hi, I need help in creating a parameterized query.
I need to show only certain fields depending on the value of the parameters. For example, I have a table with fields: ID, Name, Phone Number, Address, DOB. The parameter can only be value of A, B or C. If the user input A as the parameter in the query, only field ID, and Name will be shown If the user input B as the parameter in the query, field ID, Name and Phone Number will be shown. etc.
Is there any way to do this, rather than to separate the query into 3 queries for each of the parameters? Thank you.
i want to open a recordset, in its 'where' clause i want to pass the value which i entered in Form textbox? is this possible ?
COde: rs.open "select * from table1 where id=form.textbox",con
i have written general code above. when i run my application ..it gives error "Control which is focus can only be reference" how can i pass my value to select query ?
I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
I 'm trying to create a database for tracking time off from work and print a weekly roster. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week.
I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date2, Date3, Date4, Date,5 Date6, Date7
I now have a form. This form has the 7, fields Date1, Date2.... Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1,Date3 = [Date1]+2 and so on until I have all 7 fields showing dates from Sunday - Saturday.These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working.
I have been able to get the D1, D2.. fields to show their respective Work Week codes by writing very simple If Then statements . So here is where I AM STUCK, When I am showing this week view how do I get D1 - D7 to identify what date they are supposed to be in reference to Date1 - Date7 then compare themselves to the table Time Requests to see if they have a match and then set the value of D1, D2 . to the Time code in that table. D1 - D7 need to auto populate and do this for about 50 employees.
Is there any way to use a select query where it will select the values off of my database but temporarily use a different column name for one of the columns???
I have 3 different item names stored in my main table. When I compare this to another table, I want to select one of the item names and just pass that as item_name. This way, I can have the same compare routine instead of writing a different compare routine for each item name. Any way? Thanks a lot for the help in advance!
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
