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.
View Replies
ADVERTISEMENT
Feb 18, 2014
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.
View 14 Replies
View Related
Apr 10, 2013
I have a query with a parameter query but what I need is the information to be passed onto another parameter query automatically.
I have a Field called ITEMID and ITEMIDFK
the criteria for ITEMID is [ENTER Item ID Number]
once entered I need ITEMIDFK to use the same information inserted into the first parameter is this possible?
View 3 Replies
View Related
Oct 20, 2014
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'?
View 2 Replies
View Related
Jun 26, 2014
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.
This is an example of what I have so far
Quote:
P/N QTYWork DateWW (Work Week)
25COMP16/11/201428
25COMP26/20/201425
25COMP27/9/201428
25COMP36/20/201425
....And so on
My code is
Code:
SELECT dbo_JBKLG.JKPRT AS P/N, dbo_JOB.JBQOR AS QTY, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4)) AS [Work Date], DatePart("ww",[Work Date]) AS [WW]
FROM dbo_JBKLG LEFT JOIN dbo_JOB ON dbo_JBKLG.JKJOB = dbo_JOB.JBJNO
GROUP BY dbo_JBKLG.JKPRT, dbo_JOB.JBQOR, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4))
There are more part numbers and the data from the other parts must be in the same format in the same query.
Quote:
P/N QTY WK WW
25COMP 15506/09-06/15WW24
25COMP 22706/16-06/22WW25
25COMP 16006/23-06/29WW26
25COMP 20006/30-07/06WW27
.....And so on
View 14 Replies
View Related
Aug 29, 2013
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.
View 4 Replies
View Related
Jun 5, 2013
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
View 4 Replies
View Related
Jan 12, 2015
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.
View 14 Replies
View Related
Jun 16, 2015
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.
View 1 Replies
View Related
Nov 22, 2014
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?
View 2 Replies
View Related
Apr 11, 2014
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 ....
View 1 Replies
View Related
Dec 15, 2011
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?
View 5 Replies
View Related
Mar 15, 2013
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.
View 2 Replies
View Related
May 16, 2014
I have made a query that was supposed to update my reports every week with new inquiries that we get.
I thought i had solved this but when i opened the report this morning, it's just showing last week's.
I have this as the criteria:Between (Date()-7-Weekday(Date(),2)+1) And (Date()-Weekday(Date(),2)+1)
It's probably wrong.
View 7 Replies
View Related
Jan 16, 2007
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;
View 1 Replies
View Related
Aug 22, 2014
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.
View 3 Replies
View Related
Apr 16, 2013
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.
View 1 Replies
View Related
Nov 12, 2004
Hey..
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?
View 1 Replies
View Related
Jan 15, 2015
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.
View 2 Replies
View Related
Oct 10, 2006
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?
View 14 Replies
View Related
Jan 12, 2008
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.
View 1 Replies
View Related
Jun 25, 2005
hi ,
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 ?
thanks
View 2 Replies
View Related
Nov 3, 2014
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"
View 10 Replies
View Related
Dec 11, 2013
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.
View 2 Replies
View Related
Sep 30, 2005
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!
View 1 Replies
View Related
May 11, 2014
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.
View 3 Replies
View Related
