Pivot-like Problem

May 4, 2005

Hello,

I am a student on a apprenticeship about MS access. My assignment is to build a MS Access application. But i am stuck. I will put my problem as simple as possible.

I have 2 tables. 1 with employees:
Department(string) | EmployeeNr(AutoNr) | EmployeeName(String)


And one with a couple of times:
Time(string)


The table i need is as follows:
Department(String) | EmployeeName(String) | EmployeeNr(AutoNr) | Time1(Boolean) | Time2 (Boolean) | time3(Boolean) | ......

Unfortunatly i have absolutely no idea how to do this and it is killing me :S

Can anybody help me? Thanks in advance.
The CitiZen

View Replies


ADVERTISEMENT

Pivot Table

Oct 13, 2005

Hi,

Have Access XP on Desk top and lap top. Have about 10 various databases for different projects.

On the desk top, I cannot get a Pivot Table to open in any of the databases, but on the lap top, the same databases all have Pivot tables.

Is it possible that I have turned it off on the desk top?

Thanks.


Mike

View 3 Replies View Related

Pivot Table

Jan 6, 2006

Hi everybody.

I have a pivottable query.
This works great.

I have a column with averages.
This works great to.

Now here is my problem:
At the bottom of my Averages column there is a total (sum)

I want an average in stead of a sum.

Please help me guys....

Thank you very very much in advance...

View 3 Replies View Related

Queries :: Use ID As A Pivot

Jun 27, 2013

I work on an enterprise which we send people to do field work, & i am doing a database in Access 2013, there i have a table with all the data of our employees defined by an ID, & i have also a table with all the drivers that take em defined by another ID for them, the problem is when i am trying to make a query..I made a table with the ID of the drivers, the ID of our employees that are on that trip with him, the departure date & the arrival date; and when i try to make my query i want it to display: the both dates, & Name of the driver, his ID, & the truck he is using (all this info is on the driver table), and also to diplay the name, the ID & the rank of the employees going with him (all this info is on the employee table), the problem comes that since i am using the ID to search for every employee info, when i put that more than 1 employee is going in that trip i get this error on the top of table: Expr1000, Expr1001; instead of ID, Name, etc... & no data is displayed.

View 2 Replies View Related

Manipulate A Pivot Table

Apr 4, 2007

Is there a way that you can manipulate a pivot table, (example adding more item in a list) after the pivot table is created.

View 5 Replies View Related

Pivot Table Question

Mar 16, 2007

Is there a way to get certain parts of the pivot table to not allow the drop down selections?

View 1 Replies View Related

Pivot Tables : Total

Apr 3, 2007

Hi!

I cannot stop Access 2002 from automatic creation of Total column for every column. Is there any method to compute totals only for some columns?

I would appreciate any help!

With kind regards
Maxim Ivashkov

View 1 Replies View Related

Query To Act Like A Pivot Table???

Sep 2, 2005

Ive got a query (SearchCriteria) whose data looks like this:

Call ID....Problem Source............Problem Source2
15366....Complaint....................
15224....Collateral Order............Complaint
15734....Delivery.......................
15733....Delivery......................Order Taken
15738....Delivery.......................
15137....Complaint.....................
14238....Other.........................Delivery
15072....Complaint....................Delivery



What Im trying to do is create a query on the back of the one above which will have Problem Source in column 1 and then 2 more columns with their counts in them. So:

Problem Source......CountOfProblem Source...........CountOfProblem Source2
Collateral Order................1............................ ..................1
Complaint.......................3................. ..............................1
Delivery..........................3............... ...............................2
Other.............................1............... ...............................0


Ive created a Query with this SQL:

SELECT SearchCriteria.[Problem Source], Count(SearchCriteria.[Problem Source]) AS [CountOfProblem Source], Count(SearchCriteria.[Problem Source2]) AS [CountOfProblem Source2]
FROM SearchCriteria
GROUP BY SearchCriteria.[Problem Source];

but this gives me:

Problem Source......CountOfProblem Source..........CountOfProblem Source2
Collateral Order.................1........................... ..............1
Complaint.........................3............... ..........................1
Delivery...........................3.............. ...........................1
Other..............................1.............. ...........................1


I guess this is because it's filtering on the first Problem Source and then looking for non-blanks in the second Problem Source which isnt what Im trying to do!

Any inspiration greatly appreciated as always...

View 2 Replies View Related

Pivot Table Query

Jul 7, 2006

I have a query I saved which pulls data and a form that creates a pivot table based upon the query.

Is there a way to create a query based upon criteria such as dates to limit my recordset? I'm trying to set date values in a form and update my saved query but I cannot figure it out. Any help would be appreciated.

Here is the SQL I use to create the standard query. What I plan on including through vb is a "Revenue_Date" variable "FROM" and "TO" date in the code to limit the output based upone the dates entered.


SELECT PARENT, TYPE, SUM(TOTAL) AS COMBINED
FROM [

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'1. IND_Amount' as TYPE,
IND_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'2. SBG_Amount' as TYPE,
SBG_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'3. IND_Bonus_Amount' as TYPE,
IND_Bonus_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'4. SBG_Bonus_Amount' as TYPE,
SBG_Bonus_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'5. Licensing Fees' as TYPE,
Licensing_Fees AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'6. IND Misc Expenses' as TYPE,
IND_Misc_Expenses AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'7. SBG Misc Expenses' as TYPE,
SBG_Misc_Expenses AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'8. Other Receivables' as TYPE,
Other_Receivables AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'9. Unknown_Amount' as TYPE,
Unknown_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
]. AS BREAKOUT
GROUP BY PARENT, TYPE
ORDER BY PARENT, TYPE;

View 3 Replies View Related

Pivot Table + Query

Oct 16, 2007

I have set up a query to filter information between 2 dates using

Between [Start date] And [Finish Date] to filter the info. I'm using office 2003 which allows me to set various pivot charts based on this info with no problems at all. unfortunatly for me several other people are using office 2000 which doesnt have the same chart functionality. so i'm setting up another switch board with 2000 type charts. Right my problem as long as I dont have the Between [Start date] And [Finish Date] codes in my query I've produced the charts I require with no problems. As soon as I add the filter into the query when i go to edit the chart it comes back saying problems updating data. This I presume is because the query wants an input. Is there a way around this?????

I've spent hours searching for this, hopefully one of you can brighten my day!!!!:D

many thanks

EQ

View 2 Replies View Related

Getting Pivot Charts Out Of Access

Nov 24, 2004

a co-worker set up several pivot charts in queries, that we now need to get into a distributable format. I had envisioned having these in a report, but access doesn't agree. Is there any way of getting the chart out of access, intact, to place in a word or power point file, or into a report? My attempts to get the chart into a report have all ended with the table-like grid coming up. Surely there's a way...
thanks!

View 3 Replies View Related

Week No's On Pivot Charts

Feb 1, 2005

CAN THE FORMAT OFF THE WEEK NO USED IN PIVOT CHARTS BE CHANGED THE DEFAULT WEEK NUMBERING SYSTEM IS NOT COMPATIBLE WITH THE ENGLISH WEEK NUMBERING SYSTEM. I CAN WORK AROUND THE PROBLEM IN VBA CODE BY USING DATEPART("WW",DATE,VBMONDAY,vbFIRSTFOURDAYS) WHICH RETURNS WEEK NO IN LINE WITH THOSE USED IN ENGLAND BUT THE PIVOT CHART DOSEN'T SEEM TO OFFER THE OPTION OF WHICH WEEK NUMBERING SYSTEM TO BE USED ANY IDEAS!

View 2 Replies View Related

Pivot Table - Where To Begin

Aug 2, 2006

So I have a layout formulated in my head on how I want this to look but I'm really unsure of what all I need to setup to make this happen. I've never been fond of pivot tables and due to this don't utilize them much to know how to appropriately use it in this instance.

The attached pic file shows how I'd like the form to be setup. There will be fluff at the top that basically signifies what the record is (i.e. name of the jobsite). (A) is a drop down that will list a bunch of categories (bathroom, kitchen, laundry, etc.) Upon selecting that it will populate (C) for the subcategories relative to the main category (A). (C) is a list of part names that are associated with (A). The Column heading (B) is the Plan number which there can be 3 different plans or 5 different plans. The inside data (D) is the count of each part (C) used in each Plan (B) (i.e. 2 sinks in Plan 1, 3 sinks in plan 2, etc). Listed right below that number I want to link a picture (E) showing what that, for example, sink looks like.

So I'm not sure which tables need to be created to make this Pivot Table work. I know it's not an easy solution but if someone could take a little time to help me figure it out I'd appreciate it.

View 3 Replies View Related

Pivot Table Problem!

Nov 17, 2006

I have a pivot table which I want to associate with a table in my access database. The problem is that when I try to associate it at design time, it gives me an error saying that the database is locked and I cannot access it.

I am using access 2003. Is there any solution to this problem....like making the database unlocked. If not, I wil have to create another database with the same table which seems crazy to me.

View 1 Replies View Related

Forms :: Pivot Chart Going Over 100%?

May 9, 2014

I am writing in access 2007 and want to make a trend analysis chart based upon performance date.

I have created a query using 3 criteria from a table - date, client and performance percentage.

I've then put this into a pivotchart no problem BUT as some clients have more than one entry per day its giving me figures over 100%.

Is there a way to exclude multiple entries for any given date while retaining the other criteria ?

View 1 Replies View Related

Records Not Showing In Pivot Table

Feb 20, 2007

Hi, I hope someone can help me. I have a database as thus:

Several tables ->appended together using 'union select' into a query called 'sheet1'-> information that is coded converted via linked tables in a query called 'sheet2'

'Sheet 2' looks completely fine - it works dandy but when I try and run a pivot table not all of the values in one column that should show don't even come up as an option.

The values that are missing on the pivot report do actually exist in the query that it is running from.

There are no filters on and Pivot tables work okay on the origional tables.

Has anyone had anything similar?

Help would be most appreciated,

Thanks,

Erica

View 1 Replies View Related

How Do I Create A Query Like A Pivot Table?

Nov 15, 2005

Hi Forumers...

I have data in a table that looks like this...

SITE-ID | VALUE
Site 1 | 20
Site 1 | 21
Site 1 | 16
Site 2 | 8
Site 2 | 9
Site 2 | 12
etc...

I would like to create a query that allows me to show summary statistics for each site. eg...

SITE-ID | VALUE_MIN | VALUE_MAX | VALUE_AVG
Site 1 | 16 | 21 | 19
Site 2 | 8 | 12 | 9.667
etc...

Am relatively new to MS Access and can't work out how to create a query that does this. Any help will be appreciated.

Using MS Access 2000 (9.0.7616 SP-3) on Windows 2000

Thanks,

Chris Medlin

View 3 Replies View Related

Pivot In Query, Need First 12 Results Sumed?

May 10, 2006

ok, i am using pivot in a query. this returns 24 values, one for each hour in the day. i need to sum the values of the first 12 items returned in the query. i need them in the same query. how can this be done? the column is names from 1 through 24, 1 for each hour..

View 3 Replies View Related

Crosstab/Pivot Query Issue...

Apr 30, 2008

I have a simple crosstab query. I have source as the row heading (basically source is just string category field) and Create_Date as the column heading. This gives me a count of the tickets for each source by day.
My Issue is that when Access breaks my Date/Time field into the column heading (Format([Create_Date],"Short Date")), it must turn the date value into a string, then sort that way, because my columns come out as so:

1/18/20081/19/20081/2/2008 1/20/20081/21/2008


Notice that 1/18/2008 comes before 1/2/2008… so it is ordering the columns as a string.
How do I get Access to order the columns by date?

Thanks,
Gary

View 1 Replies View Related

Automate Pivot Export To Excel

Aug 8, 2006

Hi all, I'm using access 2002. 2002 has pivoting, but in order to use it you have to download an xp add on(at least I did). After I installed the libraries pivots worked for me- and I added all manner of niftyness-before I discovered that no one else in my department could use it without adding the same downloads. So I decided to go with exporting to excel. That opened up a whole new can of worms, but in the end I finally got it working. I just wanted to share what I found with everyone here-maybe the next person won't have to work so hard. This code works from a toolbar button.
Of course if anyone has any suggestions, I'm interested.

Public Function goToPivot()
'automates creating a formatted pivotChart in excel from a query in access2002
'because of some trickiness with objects, no 'with's are used
On Error GoTo Err_goToPivot

Dim xlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlPT As Excel.PivotTable
Dim DataRange As String
Dim ExcelFile As String
Dim queryPivot As String

'set relative path and filename of new spreadsheet
ExcelFile = Application.CurrentProject.Path & "xPivot.xls"
queryPivot = "querypivotChartTest"

' Delete file if it exists
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(ExcelFile) Then
' Delete if not read only
fso.DeleteFile ExcelFile, False
End If

'export query to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
queryPivot, ExcelFile, True

Set xlApp = New Excel.Application
xlApp.Visible = True
Set XlBook = xlApp.Workbooks.Open(ExcelFile)

'set style and range of cells, name pivotTable
DataRange = queryPivot & "!" & XlBook.Worksheets(queryPivot).UsedRange. _
Address(ReferenceStyle:=xlR1C1)
Set XlPT = XlBook.PivotCaches.Add(xlDatabase, DataRange).CreatePivotTable( _
TableDestination:="", TableName:="Pivot_Table1", _
DefaultVersion:=xlPivotTableVersion10)

'create pivotChart, preload it with fields to get user started
XlBook.Charts.Add
XlBook.ActiveChart.Location xlLocationAsNewSheet, "RCA pivot"
XlBook.ActiveChart.PivotLayout.PivotTable.AddDataF ield XlBook.ActiveChart.PivotLayout. _
PivotTable.PivotFields("SIRs"), "Count of SIRs", xlCount
XlBook.ActiveChart.PivotLayout.PivotTable.PivotFie lds("Team").Orientation = xlRowField
XlBook.ActiveChart.PivotLayout.PivotTable.PivotFie lds("Team").Position = 1


'set axes and chart titles, size and fonts of pivotChart
XlBook.ActiveChart.HasTitle = True
XlBook.ActiveChart.ChartTitle.Characters.Text = "RCA DATA ANALYSIS"
XlBook.ActiveChart.ChartTitle.Font.Bold = True
XlBook.ActiveChart.ChartTitle.Font.Size = 18
XlBook.ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
XlBook.ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "CATEGORY"
XlBook.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
XlBook.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "TOTAL"
XlBook.ActiveChart.SizeWithWindow = True



'optional-create, save, then close
'XlBook.Saved = True
'xlApp.Quit
'Set XlPT = Nothing
'Set XlBook = Nothing
'Set xlApp = Nothing


Exit_goToPivot:
Exit Function

Err_goToPivot:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_goToPivot

End Function

View 1 Replies View Related

Refresh Pivot Table In Form

Sep 21, 2006

I'm trying to use a PivotTable Form (in Access), in order to automate a process (previously data was exported to Excel and the pivot table was created manually). I've got my pivot table form working fine but I need to provide the user with a way to refresh the Pivot table - i.e. re-run the underlying query.

In Access Help it says to select design view and then click the Refresh button (the one with the big red '!' icon). This does work and the data is updated but it's not a user friendly option.

Opening the form doesn't automatically refresh the pivot either, so does anyone know a way I can provide a button with VBA code or whatever to manually update the pivot so it reflects changes in the underlying data?

View 1 Replies View Related

Access Pivot Charts In Powerpoint

Apr 1, 2004

I have created a pivot chart using the Autoform and now i want to export this charts to powerpoint, i cannot copy that chart which i have created in in autoform, i can export the pivot table to excel sheet and draw again the chats but i cant copy or export the pivot charts directly, Is there any option for that. can anybody help me in this case?

View 2 Replies View Related

Pivot Table Datasource Security

Nov 24, 2005

Hi,

Not sure what section this should go in but it vaguley relates to access and VBA so it's in here.

After a complete nightmare trying to do some decent graphs in ASP i decided to try using excel linked to my Access DB.

I've set up my pivot tables in Excel to link to the Db on the webserver. The user can open the workbook from the website and view the reports and graphs.

The problem is the database contains sensitive data for more than one organisation.

I have a workbook for each organisation selected dynamically when the user logs in to the webpage.

I've set a parameters in the pivot table querys so that all querys will be filtered using the users organisation code.

Trouble is you can access the data source of the pivot table through the wizard and ammend the parameter to view other organisations data.

Is there a way of preventing the user from accessing this feature i.e locking the wizard?


Thanks

TS

View 1 Replies View Related

Dynamically Associating A Pivot Table !!

Oct 30, 2006

I have a pivot table, It is associated with a table in an another database (access database ofcourse!). What I am doing is that I am inserting new data on a click of a button in that table but the pivot table shows the same old data, it does not refresh..!!

How can I do that??

View 5 Replies View Related

Reports :: Report Of Pivot With Dates

Jun 14, 2015

I have a table with columns

position, DateTime, value
(datetime being a text field by the way, not my fault :-))

I wrote a simple pivot query

TRANSFORM Avg(value) AS AvgOfvalue
SELECT Position
FROM table
WHERE (dateTIME)>format(Date()-2,"DD-MMM-YY")
GROUP BY position
PIVOT dateTIME;

The idea was that I could report each last 2 days with this query.The pivot query works OK.Creating a report based on this query, the control source of the fields is linked to columns like "12-JUN-15 07:00" which will not be available after some days.Isn't it possible to create a pivot report on a pivot query based on dates?

View 8 Replies View Related

Modules & VBA :: Display A Pivot Chart

Nov 18, 2013

I have a need to display a pivot chart by clicking a button on my menu screen. At the moment my button will just display the query results and if I then click on View/PivotChart View my chart displays.

Code:
Private Sub Command316_Click()
DoCmd.OpenQuery "R06X - OOS Chart", avViewNormal
End Sub

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved