473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

A resaonable approach to managing data?

Folks,

I at the proverbial fork in the road and before I make a decision, I
was hoping maybe I could get some advice. I'm in the process of moving
data to Access. I'd like to use Excel for charting.

Consider this typical scenario for a second, and decide let me know how
you would handle it.
1) 210,000 record table exists in Access
2) Data request came in as follows: number of deer checked at each
check station, by county, by district - very simple
3) Created a query to grab just the fields I needed. Saved the query.
4) Started Excel and pulled the data via MS QUERY.
5) Brought the data into Excel as a Pivot Table.
6) Manipulated a couple of things and sent the workbook as an email
attachment.
7) Saved the workbook.

To me, seems like a lot of steps and duplication of data. I've stored
data in Access and Exel both. If someone wants that Pivot Table
resent, I've got to try and remember where I put it. I'm trying to
cut down on duplication and clutter. Seems like I should have done
this all from Access. Yes/no?

I would love to hear from others on how they would have handled this.
I might mention, I probably will need a similar table next year, with
next year's data. Other than that, I'll probably never use that
particular Pivot Table again.

Thank you so much for all of your help.

Mike

Jul 21 '06 #1
14 1660
Excel is definitely easier for data manipulation, but with some average
coding skills you could probably reproduce the Excel part in Access,
thus reducing duplication of data.

I know most people are really upset Access doesn't have a pivot
function to change columns to rows, but if you do a little research on
arrays you can create your own pivot function. It's not as fast as,
say, SAS's "Proc Transpose", but it does work.
Takeadoe wrote:
Folks,

I at the proverbial fork in the road and before I make a decision, I
was hoping maybe I could get some advice. I'm in the process of moving
data to Access. I'd like to use Excel for charting.

Consider this typical scenario for a second, and decide let me know how
you would handle it.
1) 210,000 record table exists in Access
2) Data request came in as follows: number of deer checked at each
check station, by county, by district - very simple
3) Created a query to grab just the fields I needed. Saved the query.
4) Started Excel and pulled the data via MS QUERY.
5) Brought the data into Excel as a Pivot Table.
6) Manipulated a couple of things and sent the workbook as an email
attachment.
7) Saved the workbook.

To me, seems like a lot of steps and duplication of data. I've stored
data in Access and Exel both. If someone wants that Pivot Table
resent, I've got to try and remember where I put it. I'm trying to
cut down on duplication and clutter. Seems like I should have done
this all from Access. Yes/no?

I would love to hear from others on how they would have handled this.
I might mention, I probably will need a similar table next year, with
next year's data. Other than that, I'll probably never use that
particular Pivot Table again.

Thank you so much for all of your help.

Mike
Jul 21 '06 #2
"ManningFan" <ma********@gmail.comwrote
I know most people are really upset Access
doesn't have a pivot function to change
columns to rows, but if you do a little
research on arrays you can create your
own pivot function. It's not as fast as,
say, SAS's "Proc Transpose", but it
does work.
I don't know _where_ you manage to get all your (mis-)information. Access
has, since the 16-bit days, had CrossTab Queries which change columns to
rows; and for the last two versions, Access 2002 and 2003, has had both
Pivot Table and Pivot Chart functionality, in addition.

Larry Linson
Microsoft Access MVP


Jul 21 '06 #3
CrossTab queries don't really do the trick, and they certainly don't
work like pivot tables or there'd be no need to "include" them in
Access 2002/03.

I'm still using A2K, so I didn't know about the new functionality. As
of A2K if you wanted to do a true transposition of data you had to do
it with an array.

Peyton Manning
Microsoft Access MVP

Larry Linson wrote:
"ManningFan" <ma********@gmail.comwrote
I know most people are really upset Access
doesn't have a pivot function to change
columns to rows, but if you do a little
research on arrays you can create your
own pivot function. It's not as fast as,
say, SAS's "Proc Transpose", but it
does work.

I don't know _where_ you manage to get all your (mis-)information. Access
has, since the 16-bit days, had CrossTab Queries which change columns to
rows; and for the last two versions, Access 2002 and 2003, has had both
Pivot Table and Pivot Chart functionality, in addition.

Larry Linson
Microsoft Access MVP
Jul 21 '06 #4
"ManningFan" <ma********@gmail.comwrote
I'm still using A2K, so I didn't know
about the new functionality. As
of A2K if you wanted to do a true
transposition of data you had to do
it with an array.

Peyton Manning
Microsoft Access MVP
Well, "Peyton," (or, maybe that should be "Don" or "Steve"?) perhaps you
ought to submit your information for posting in the MVP Awardees section at
http://mvp.support.microsoft.com. It's really easy to do once you log in
with your authenticating information, and that way, people could verify that
you aren't just making a false claim to being a Microsoft Access MVP and
that your use of the title isn't just more mis-information as you posted
earlier in this thread.

Larry Linson
Microsoft Access MVP

For information about what the MVP program is and who the MVPs are, visit
http://mvp.support.microsoft.com.
Jul 21 '06 #5
ManningFan wrote:
I know most people are really upset Access doesn't have a pivot
function to change columns to rows
How do you know that?

Jul 22 '06 #6

Lyle Fairfield wrote:
ManningFan wrote:
I know most people are really upset Access doesn't have a pivot
function to change columns to rows

How do you know that?
Must have heard us sniffling and saw us wiping our eyes.

Jul 22 '06 #7
Because I talk to alot of users.

Peyton Manning
Microsoft Access MVP

Lyle Fairfield wrote:
ManningFan wrote:
I know most people are really upset Access doesn't have a pivot
function to change columns to rows

How do you know that?
Jul 24 '06 #8
"ManningFan" <ma********@gmail.comwrote
Because I talk to alot of users.

Peyton Manning
Microsoft Access MVP
Well, "Peyton," (or, maybe that should be "Don" or "Steve"?) perhaps you
ought to submit your information for posting in the MVP Awardees section at
http://mvp.support.microsoft.com. It's really easy to do once you log in
with your authenticating information, and that way, people could verify that
you aren't just making a false claim to being a Microsoft Access MVP and
that your use of the title isn't just more mis-information as you posted
earlier in this thread.

Larry Linson
Microsoft Access MVP

Jul 25 '06 #9

ManningFan wrote:
Because I talk to alot of users.

Peyton Manning
Microsoft Access MVP

Lyle Fairfield wrote:
ManningFan wrote:
I know most people are really upset Access doesn't have a pivot
function to change columns to rows
How do you know that?
"A lot of users" (that you talk to) = "most people"? The notion of
changing columns to rows is entirely at odds with what a database is;
it's like being upset because cabbages don't come with their own
condoms.

Jul 25 '06 #10
"The notion of changing columns to rows is entirely at odds with what a
database is"

Stupider words were never spoken...

I guess the folks at SAS need to be made aware that their "Proc
Transpose" (which has been in use for over 10 years) is useless, and
the folks at Microsoft need to be told that adding pivot functionality
to Access is a stupid idea. Hell, while we're at it let's remove the
Pivot command from SQL Server 2005 since it goes against the grain.

Because Lyle has all the answers, right?

Peyton Manning
Microsoft Access MVP

Lyle Fairfield wrote:
"A lot of users" (that you talk to) = "most people"? The notion of
changing columns to rows is entirely at odds with what a database is;
it's like being upset because cabbages don't come with their own
condoms.
Jul 25 '06 #11
ManningFan wrote:
I guess the folks at SAS need to be made aware that their "Proc
Transpose" (which has been in use for over 10 years) is useless, and
the folks at Microsoft need to be told that adding pivot functionality
to Access is a stupid idea. Hell, while we're at it let's remove the
Pivot command from SQL Server 2005 since it goes against the grain.

Because Lyle has all the answers, right?
Useless is in the eye of the beholder; it's useless to me.
Yes.
Yes.
Sometimes, Yes; sometimes, No.

Jul 25 '06 #12
On 25 Jul 2006 05:55:46 -0700, "Lyle Fairfield" <ly***********@aim.comwrote:
>ManningFan wrote:
>I guess the folks at SAS need to be made aware that their "Proc
Transpose" (which has been in use for over 10 years) is useless, and
the folks at Microsoft need to be told that adding pivot functionality
to Access is a stupid idea. Hell, while we're at it let's remove the
Pivot command from SQL Server 2005 since it goes against the grain.

Because Lyle has all the answers, right?

Useless is in the eye of the beholder; it's useless to me.
Yes.
Yes.
Sometimes, Yes; sometimes, No.
Fools rush in ....
IMO it is is sometimes useful to present data with the records arranged or grouped vertically
rather than horizontally, particularly for calculations, which is what pivots (which use aggregate
functions) do.
I think Lyle's outrage is partly because he uses "rows" to mean "records" rather than "horizontal
arrangements for presentational purposes". Transposing an actual table which represents real-world
objects is unlikely to be very sensible.
Jul 25 '06 #13
"Larry Linson" <bo*****@localhost.notwrote in message
news:%ndxg.10882$Oz3.8780@trnddc02...
"ManningFan" <ma********@gmail.comwrote
Because I talk to alot of users.

Peyton Manning
Microsoft Access MVP

Well, "Peyton," (or, maybe that should be "Don" or "Steve"?)
It's not Steve (PCD). The English is far too good and there aren't nearly
enough of these: "!!!!!!".

Keith.
Jul 25 '06 #14
Keith Wilby, King of Grammar!
!!
!!!

Peyton Manning
Microsoft Access MVP

Keith Wilby wrote:
It's not Steve (PCD). The English is far too good and there aren't nearly
enough of these: "!!!!!!".

Keith.
Jul 25 '06 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ken Fine | last post by:
I periodically receive a 5+ MB XML document that I hand-load into SQL Server using SQLXML running under a DTS process. Unfortunately, the document is human-created, and (very unfortunately) often...
2
by: jason | last post by:
the enterprise is going to eventually convert the existing ASP Classic website to ASP.NET until that time, development has already begun for a C# library of business objects. for the most part,...
0
by: Jason Mauss | last post by:
Over the past few days (in my spare time in the evenings) I've been trying to think of a table design that would best lend itself to paging the content for the purpose of paging articles on a...
14
by: Nick Gilbert | last post by:
Hi, I have an asp.net application which runs from a CD-ROM using Cassini. As such, it is single user only. The application connects to an Access database when it is loaded, and keeps the same...
11
by: c676228 | last post by:
Hi everyone, I am just wodering in asp program, if there is anybody writing store procedure for inserting data into database since there are so many parameters need to be passed into store...
5
by: Stan SR | last post by:
Hi, Some newbie questions.. :-) First, what is the namespace to use for the Cache class ? When I use this bit of code I get an error if (Cache==null) Cache.Insert("myUserList",userlist);...
1
by: AMDRIT | last post by:
Occasionally I try my hand at a simple data storage engine. Today I ran across an article on the web http://msdn2.microsoft.com/en-us/library/aa289151(vs.71).aspx, and it got me thinking again. ...
10
by: pbd22 | last post by:
Hi. Like the title says - how do i do this? I was given the following example: INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A' The above statement threw the following error:
9
by: kirk | last post by:
I have program.cs, my "main" form and then a "settings" form. My "main" form existed for awhile and I had constants, instantiations, properties, etc within it created. I went to create my...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.