473,326 Members | 2,099 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,326 software developers and data experts.

Help save me a day's work... (please)

hello-
I'm no olympic swimmer in the big pool of MS Access, but i can doggie
paddle my way through it. at this university i just got a job at, they
are using Excel to seperate survey data based on fields. They manually
have to filter, copy & paste to a new file, save it based on the content
of the filtered field, and repeat for each unique value in the field. an
example may be good right now. To try to make this simpler, we'll focus
on one school, in the real problem there are 5 schools...
SCHOOL COURSE SECTION INSTRUCTOR (remaining fields)
eng 101 01 Bob (15 records)
eng 101 02 Joe (13 records)
eng 201 01 Ted (09 records)
eng 201 02 Bob (06 records)
....

(Hope this isn't too confusing yet).

Then, we have to parse the file two ways: (1)by school--with averages of
the remaining fields by course, by section, and overall. (2)by
Instructor--with averages by course, by section, and overall.
With these parsed files, we need to include the raw data and embed the
averages in the file. so the final file would read:
CASE SCHOOL COURSE SECTION INSTRUCTOR VARA VARB
01 eng 101 01 Bob 4 5
02 eng 101 01 Bob 3 3
section average 3.50 4.00
03 eng 101 02 Joe 3 4
04 eng 101 02 Joe 3 3
section average 3.00 3.50
course average 3.25 3.75
03 eng 201 01 Ted 2 4
04 eng 201 01 Ted 4 4
section average 3.00 4.00
course average 3.00 4.00
....
overall average 3.17 3.83

Knowing the little bit i know about Access, i think there must be a
simpler way to do this. a report? a macro? i dunno. but there has to be
an easier way...right?
Many many many many thanks.
Nov 12 '05 #1
3 1532
If you import the detail into ms access and then create two queries -
one grouping by school and the other by instructor - you could then
recopy to excel, create a report or whatever else you would like to do

MyName <My**@il.com> wrote in message news:<7c******************************@news.terane ws.com>...
hello-
I'm no olympic swimmer in the big pool of MS Access, but i can doggie
paddle my way through it. at this university i just got a job at, they
are using Excel to seperate survey data based on fields. They manually
have to filter, copy & paste to a new file, save it based on the content
of the filtered field, and repeat for each unique value in the field. an
example may be good right now. To try to make this simpler, we'll focus
on one school, in the real problem there are 5 schools...
SCHOOL COURSE SECTION INSTRUCTOR (remaining fields)
eng 101 01 Bob (15 records)
eng 101 02 Joe (13 records)
eng 201 01 Ted (09 records)
eng 201 02 Bob (06 records)
...

(Hope this isn't too confusing yet).

Then, we have to parse the file two ways: (1)by school--with averages of
the remaining fields by course, by section, and overall. (2)by
Instructor--with averages by course, by section, and overall.
With these parsed files, we need to include the raw data and embed the
averages in the file. so the final file would read:
CASE SCHOOL COURSE SECTION INSTRUCTOR VARA VARB
01 eng 101 01 Bob 4 5
02 eng 101 01 Bob 3 3
section average 3.50 4.00
03 eng 101 02 Joe 3 4
04 eng 101 02 Joe 3 3
section average 3.00 3.50
course average 3.25 3.75
03 eng 201 01 Ted 2 4
04 eng 201 01 Ted 4 4
section average 3.00 4.00
course average 3.00 4.00
...
overall average 3.17 3.83

Knowing the little bit i know about Access, i think there must be a
simpler way to do this. a report? a macro? i dunno. but there has to be
an easier way...right?
Many many many many thanks.

Nov 12 '05 #2
I would think you can create queries for each filter and then paste
the results into Excel. Maybe you could combine filters if the
criterias wouldn't exclude each other.

Try making queries with as much of the data as can be filtered at one
time, with as many filter criterias as will still be accurate, and go
from there. Also you could use a Totals query to find the averages
and such.

Regards
Julia

MyName <My**@il.com> wrote in message news:<7c******************************@news.terane ws.com>...
hello-
I'm no olympic swimmer in the big pool of MS Access, but i can doggie
paddle my way through it. at this university i just got a job at, they
are using Excel to seperate survey data based on fields. They manually
have to filter, copy & paste to a new file, save it based on the content
of the filtered field, and repeat for each unique value in the field. an
example may be good right now. To try to make this simpler, we'll focus
on one school, in the real problem there are 5 schools...
SCHOOL COURSE SECTION INSTRUCTOR (remaining fields)
eng 101 01 Bob (15 records)
eng 101 02 Joe (13 records)
eng 201 01 Ted (09 records)
eng 201 02 Bob (06 records)
...

(Hope this isn't too confusing yet).

Then, we have to parse the file two ways: (1)by school--with averages of
the remaining fields by course, by section, and overall. (2)by
Instructor--with averages by course, by section, and overall.
With these parsed files, we need to include the raw data and embed the
averages in the file. so the final file would read:
CASE SCHOOL COURSE SECTION INSTRUCTOR VARA VARB
01 eng 101 01 Bob 4 5
02 eng 101 01 Bob 3 3
section average 3.50 4.00
03 eng 101 02 Joe 3 4
04 eng 101 02 Joe 3 3
section average 3.00 3.50
course average 3.25 3.75
03 eng 201 01 Ted 2 4
04 eng 201 01 Ted 4 4
section average 3.00 4.00
course average 3.00 4.00
...
overall average 3.17 3.83

Knowing the little bit i know about Access, i think there must be a
simpler way to do this. a report? a macro? i dunno. but there has to be
an easier way...right?
Many many many many thanks.

Nov 12 '05 #3
Why not just create the tables with the required fields and import
your excell file. It appears the output you posted here is tab
delimited so it should just move over with some exceptions. those
exceptsions will show up as errors and you can figure out why and fix
accordingly. You will have to decide what you want to import as well.

Dannic
http://www.orpgs.com

MyName <My**@il.com> wrote in message news:<7c******************************@news.terane ws.com>...
hello-
I'm no olympic swimmer in the big pool of MS Access, but i can doggie
paddle my way through it. at this university i just got a job at, they
are using Excel to seperate survey data based on fields. They manually
have to filter, copy & paste to a new file, save it based on the content
of the filtered field, and repeat for each unique value in the field. an
example may be good right now. To try to make this simpler, we'll focus
on one school, in the real problem there are 5 schools...
SCHOOL COURSE SECTION INSTRUCTOR (remaining fields)
eng 101 01 Bob (15 records)
eng 101 02 Joe (13 records)
eng 201 01 Ted (09 records)
eng 201 02 Bob (06 records)
...

(Hope this isn't too confusing yet).

Then, we have to parse the file two ways: (1)by school--with averages of
the remaining fields by course, by section, and overall. (2)by
Instructor--with averages by course, by section, and overall.
With these parsed files, we need to include the raw data and embed the
averages in the file. so the final file would read:
CASE SCHOOL COURSE SECTION INSTRUCTOR VARA VARB
01 eng 101 01 Bob 4 5
02 eng 101 01 Bob 3 3
section average 3.50 4.00
03 eng 101 02 Joe 3 4
04 eng 101 02 Joe 3 3
section average 3.00 3.50
course average 3.25 3.75
03 eng 201 01 Ted 2 4
04 eng 201 01 Ted 4 4
section average 3.00 4.00
course average 3.00 4.00
...
overall average 3.17 3.83

Knowing the little bit i know about Access, i think there must be a
simpler way to do this. a report? a macro? i dunno. but there has to be
an easier way...right?
Many many many many thanks.

Nov 12 '05 #4

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

Similar topics

3
by: laurie | last post by:
Hi all, I'm trying to help out a friend who has inherited a client with a PHP shopping cart application. Neither of us know PHP, but I've been muddling my way through, trying to get these old...
8
by: Shlomi Schwartz | last post by:
Hi all, Why do I get the folowing date when creating it like so: var d = new Date(2003,9,2); Wed Oct 1 23:00:00 UTC+0200 2003 Isent it soposed to be:
10
by: Jack Sadie | last post by:
I am using Windows XP and I have found out how to substitute my own wav sound for the Microsoft default sound when my computer starts, but now I want to go a step further. I have created 7...
0
by: U S Contractors Offering Service A Non-profit | last post by:
This Sunday the 26th 2006 there will be Music @ Tue Nov Inbox Reply Craig Somerford to me show details 9:54 pm (26 minutes ago) #1St "CLICK" HeAt frOm A blanket...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
2
by: Bill | last post by:
I have a 200 record database that includes a date/time field, AnnivDate, for a wedding anniversary. AnnivDate has nulls and some incorrect year data. I have been creating the Access database...
4
by: Melissa | last post by:
I currently have VBA written to export query results into an Excel file. That file is then formatted using the code below. The problem I'm having is that it keeps throwing Error 91 (Object...
12
by: kang jia | last post by:
hi currently i am doing this car booking website. i would like to only update our inventory one day before customer's start booking time, thus before that, i am still able to rent to other...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
6
by: cj2 | last post by:
I need to create a cover letter in VB. It will have a customers account number at the top and be addressed to them by name for example (Dear Mr. Smith). After this it will be the same text for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.