Can Show Two Column Names From A Query In X Axis Barchart?
Nov 29, 2011
Select Year,Type,Count(*) from Table group by Year,Type;
I am running the above query to create a bargraph:
Result of my query:
Year Type Count
---- ----- ------
OCt-10 Type1 5
Apr-11 Type 4 4
Expected graph:
Y axis Counts
X axis Year and then Type together
Two bars in total :
One bar for Oct-2010,Type 1 showing a count of 5
One bar for Apr-2011,Type 4 showing a count of 4
Is it possible to show Year as well as the Type values as labels in X axis in a barchart. I know it can be done with pivot charts but I dont know how to create it as a report with pivot barchart .
I'm tracking the holiday entitlement of a team of people. I use a query to work out how much unbooked holiday they have to take.
My problem is where I'm scheduling next year my query returns the names of those who have booked a holiday and their remaining entitelement. That's as it should be. However if someone hasn't yet booked any holidays then it simply doesn't display their record. I would like it to treat that record as zero and show the remaining entitlement as a full years entitlement.
Here's the SQL SELECT Employees.Trainer_Name, Sum([2015 Holiday].[2015 Days]) AS [SumOf2015 Days], Employees.Holiday_Days, [Employees]![Holiday_Days]-[SumOf2015 Days] AS 2015 FROM [2015 Holiday] INNER JOIN Employees ON [2015 Holiday].Trainer_Name = Employees.Trainer_Name GROUP BY Employees.Trainer_Name, Employees.Holiday_Days;
The problem here is that the Sum of 2015 holiday is Null
I wonder how can I list all the column names for a given table using a single query? I know how to do it in SQL using system tables, but no idea how to do that in Access.
When I run a query, whenever the criteria is not met, I want the column header for the row to not show up in the query.
I am running a select query and I have 10 items on the query, I select what I want via check box and then hit OK to run the query. I want the fields that are not checked to not show up in the query at all. Right now I still get the column header, any way to get rid of it. Thanks a lot for any help.
I just started my internship a couple weeks ago so I am still relatively new, but here's my problem...I have created a query to run the weekly statistics for my data but one column continues to show multiple times. I want the "Left in Estock" and "Left in Estock Line Items" to only show once, instead of all the way down the side. Check out the attachments.
In forms, I frequenty use the following expression to get the results needed from a field's column: =EmployeeID.column(1).
However, when I try to use the column function in a query it doesn't like it: EmployeeName:[EmployeeID].Column(1)
What am I doing something wrong?
Along the same line, if I want to use =EmployeeID.column(1) for an unbound control in a form, why must you put the bound field on the form just to get the info? In this case, I want to do this to display the employee's name - not the primary key - without the scroll bar. Every time I do this, Access performance analyser tells me to use fewer controls, but you can't if you have to have the reference. The same thing seems to be required in queries, i.e. include the unaltered control to get an expression.
Help and advise, as always, will be appreciated! Christine
I have created a MS Access barchart by running a query. X axix has user name and y axis has counts. I would like to change every bar's color based on the x axis user name value .Is there a way to do that using vba code in MS Access barcharts.
I am trying to create an ASP script that I can use to take the column names out of my access database, and then take out the data. I have a select statment like this:
Code: <%Set conn = Server.CreateObject("ADODB.Connection")conn.open "source","",""SQL_query = "SELECT * FROM table"Set RS = conn.Execute(SQL_query)%><%WHILE NOT RS.EOF%><%=RS("field")%><%RS.MoveNext WEND%>
I would just like to make it so the above script just takes out each column header from my database table, and instead of the <%=RS("field") it shows the current column name.
I have a project of migrating our Access data to Oracle. One of the pre-reqs the boss wants is to uppercase all of the column names (Field name) for all of the tables. I want to avoid manually going in all the tables and changing the case of the columns because there is like 300 tables where dealing with. I was wondering if in Access..is there an efficient way of converting my column names to upper case via script? VBA or Macro?
I have 2 or 3 Queries which are run specifically export data to an Excel file required by external agencies.The queries contain column headings, required by the agencies, which are not derived from Table fields. Obviosly, no record data is produced for these columns.When the queries are run, the dreaded 'Enter Parameter Value' message appears for each to the additional column headings.
The following is a copy of the SQL Code for one such Query;
SELECT DISTINCTROW [Name1] AS Title, [Mail List].[First Name], [Mail List].Surname, [Mail List].[Address 1], [Mail List].PostCode, [Name2] AS [Aggregated Donations], [Name3] AS [Sponsored Event], [Mail List].SubDate, Sum([Mail List].[ Recd]) AS [SumOf Recd] FROM [Mail List] GROUP BY [Name1], [Mail List].[First Name], [Mail List].Surname, [Mail List].[Address 1], [Mail List].PostCode, [Name2], [Name3], [Mail List].SubDate, [Mail List].[Gift Aid] HAVING ((([Mail List].SubDate)>#4/4/2014# And ([Mail List].SubDate)<#4/6/2015#) AND (([Mail List].[Gift Aid])=True));
Is there not some way in which these columns can be defined in Property Sheet such that this error message is prevented?
I am trying to create a rolling twelve month query that will total the months across. I set the query up so that it is a rolling twelve month, but I am having difficulty getting it to list the months across. I would like it to take the first month, list it as a column and show the totals according and the same with each month after. Here is the query that I currently have:
SELECT WIPHistoryTbl.WIP_PART_NUM, WIPHistoryTbl.WIP_MONTH, WIPHistoryTbl.WIP_YEAR, SpinsMonthEndTbl.WEEKS_IN_MONTH, WIPHistoryTbl.WIP_AMOUNT, IIf(Count(Switch([WIP_MONTH_NUM]='06',1))=1,Sum([WIP_AMOUNT]),' ') AS JUNE_TOTALS FROM SpinsMonthEndTbl INNER JOIN WIPHistoryTbl ON (SpinsMonthEndTbl.SPINS_YEAR = WIPHistoryTbl.WIP_YEAR) AND (SpinsMonthEndTbl.ACCT_MONTH = WIPHistoryTbl.WIP_MONTH_NUM) GROUP BY WIPHistoryTbl.WIP_PART_NUM, WIPHistoryTbl.WIP_MONTH, WIPHistoryTbl.WIP_YEAR, SpinsMonthEndTbl.WEEKS_IN_MONTH, WIPHistoryTbl.WIP_AMOUNT, WIPHistoryTbl.WIP_MONTH_NUM, SpinsMonthEndTbl.BEG_DATE, SpinsMonthEndTbl.END_DATE HAVING (((SpinsMonthEndTbl.BEG_DATE) Between (Now()-365) And (Now()))) ORDER BY WIPHistoryTbl.WIP_YEAR, WIPHistoryTbl.WIP_MONTH_NUM;
I would really like it if the query could look at the BEG_DATE, select the corresponding ACCT_MONTH and show it as the column header.
These are what I would like my results to look like: PART_NUM | 06_TOTALS | 07_TOTALS | 08_TOTALS . . . . . . . . . 123456789 | 10 | 5 | 15 .
I am using Access 2007 and would like to know how to copy column names from one table to another. I am importing data from excel and the column names come out as 'Field 1', 'Field 2', etc. I went through the trouble of manually renaming the column names of one of the tables (22 columns in all). I will be importing/creating 55 new access tables and don't want to have to manually rename all of these columns. All of the columns in all of the tables will be exactly the same, so I was hoping that after I did this once, I could copy the column names to the other 54 tables.
How do you display the names of field for a table in a combo box. I know how to do it in mySQL "DESCRIBE employee_data", is there a SQL command in access to do this?
I used the code below to check the empty fields in the form and populate it in a message
Code: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim ctr As Control Dim strMsg As String 'Loop through every control on the form For Each ctr In Me.Controls
[code].....
I want the message to show the captions of controls instead of its names
In Tbl_Output's 3rd column, only the Columns names: Name, Age and Location are repeated for each person and not column names ID1,ID2 (only its data xxx,yyy etc. is required in columns 1 and 2 as shown).
I was helped by rpeare with a VBA module that gives a single column output in Tbl_Output as
Mike 25 Essex Jack 32 Surrey Bob 36 Newcastle
The code is:
Sub main()
Dim db As Database Dim rstElements As Recordset Dim sName As String Dim sNumber As String Dim sArea As String Dim freefile Dim Filenumber As Integer Dim sSQL As String
Set db = CurrentDb Set rstElements = db.OpenRecordset("tbl_elements") rstElements.MoveFirst
sSQL = "DELETE * FROM Tbl_Output" db.Execute sSQL
Do While rstElements.EOF <> True sName = rstElements.Fields(1) sNumber = rstElements.Fields(2) sArea = rstElements.Fields(3)
My employer wants me to update and revise a form with almost a hundred controls. All of the information is stored in a single table.
Is there a way that I can (easily) sort the order of columns in the table to alphabetize them? I know how to sort and filter records, is there a way to sort the columns other than the manual click and drag solution?
Note: I know that the database I'm working on for this project is not normalized. My employer isn't concerned with normalization. I've made as many corrections to the table as possible, already, but some things just won't be fixed.
I have a spreadsheet which contains data exported from another system (which I have no control over). I'm using this spreadsheet as a linked table in my database.
The problem I am having is that I can't guarantee that when the spreadsheet is updated, it will contain the same column headings as it did the last time...
The spreadsheet contains a list of temps, with a summary of info off their submitted timesheets. So the column headings (as well as WorkerName, TimesheetDate, etc) may be "Standard Hour", "Overtime Hour", "Over 12 hrs Hour", "Standard Day", "Overtime Day", etc - for each type there will be a Pay Rate and a number of units (hours or days) claimed. The columns only appear if 'someone' in the spreadsheet has claimed something under that heading this week.
What I need to do is to produce a report which gives a summary by person and week of the number of hours claimed and the total charge. I've done that - that part was easy The part I'm struggling with, is how to take the column headings and turn those into descriptors for each charge type... in otherwords, to go from the sample 'timesheet' below to the sample 'ByType' ?
... when I don't know which columns will be present each week?
At the moment I'm using a union query to pull out the info I need, but if the column headings change then I know it will stop working...
sample of my union query... I currently have 8 different sets of bill rate and charge rate, this just does the first couple...
Code: SELECT qryTimesheetBaseData.[Time Sheet Start Date], qryTimesheetBaseData.[Time Sheet End Date], qryTimesheetBaseData.[Cost Centre], qryTimesheetBaseData.Worker, "Standard" AS RateType, "Hourly" AS RateCategory, qryTimesheetBaseData.[Bill Rate (ST/Hr)] AS Rate, qryTimesheetBaseData.[Time Sheet Billable Hours (ST/Hr)] AS Billable, qryTimesheetBaseData.[Time sheet Status] AS Status
Is there a way of changing the column heading names on the split form portion of an access form. I tried changing them using a query to populate the form and changing the caption of each field in the query and that didn't work. I also tried adding a [Caption] at the design level of the table and that had no affect also. The split form seems to be displaying a portion of the actual field name or something like that.
