All,

I have a spreasheet that comes from one of our systems every other
week. Say it contains a record for each bowler, with their ID and
then Columns P1 to P10.

The values in P1 to P10 indicate how many pins they knocked down in
each frame of bowling.

They are all lousy bowlers.

Here are four sample records

Fred |0|0|0|3|9|7|0|0|5|9|
Barney |0|0|0|3|5|7|0|4|2|0|
Wilma |9|0|8|3|9|7|0|0|5|0|
Betty |1|0|8|3|9|7|0|0|0|0|

Here is what I need in a new or updated table:

Fred |3|9|7|5|9|-|-|-|-|-|
Barney |3|5|7|4|2|-|-|-|-|-|
Wilma |9|8|3|9|7|5|-|-|-|-|
Betty |1|8|3|9|7|-|-|-|-|-|

Would someone please point me towards the correct approach to
transmorgaphying the data as shown? I don't expect an entire
solution, but I have no idea where to start...but I bet it involves
"if" and a custom function.

Thanks,

Patrick

Hi Patrick,

For using a system like Access (or sql Server/Oracle) which is/are a
relational database management systems (RDBMS) your data table should
look more like this:

Name Frame Pins Game
Fred 1 3 1
Fred 2 9 1
Fred 3 7 1
...
Barney 1 3 1
Barney 2 5 1
Barney 3 7 1
...


With this format, it becomes much easier to query. RDBMS databases read
from top down - they don't read across. You can display data across
like a spreadsheet for reporting. But for adding data to the system you
should add it from top down. This may seem a little cumbersom at first,
but once you get the idea (it is called Normalization) you will see this
is the way to go. I am sure your source data in the Excel file is
displayed across. So you will have to transpose the data to get it into
this format - either in Excel before the data import - or in Access
after the data import -- if you import the data cross wise (meaning
importing it the way you see it in Excel).

Here is some logic for following my advice: If you are going to put
forth the effort to store your data in an Access database you should do
it correctly. But you don't have to. You can store your data in Access
the same way you see it in Excel - cross wise without transposing the
data. But doing that would be a waste of time because you could look up
stuff in Excel (with look up tables) with less effort than you could in
Access if you stored your data cross wise (unNormalized). But there is
no law that says you have to store your data in Access in a Normalized
state. You will just be doing twice the work when it comes to looking
up information.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Rich,

Thanks for the explanation. Please allow me to clarify...

I don't intend to store the data in Access. I am actually not going
to store the data anywhere. I just need to change the "look" of the
data and send it off to a 3rd party. My job is to create a simple (to
use) process that will "translate" the data that comes from a legacy
system looking one way, and needs to go to a vendor looking another
way.

At the moment, someone has to do this by hand every other week for a
500 line, 10 column report. I was hoping Access would offer the tools
I need to create a "widget" that would speed the process along.

Patrick


On Jan 15, 6:32*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:

OK. So what I am hearing is that you receive an Excel Spreadsheet
(actually - you only said spreadsheet - I assume it is in Excel) which
contains some data, and someone has to edit this data in the same
spreadsheet every other week - and you want to automate the editing
process. It looks like the edits you want are to edit the values of 0
to a dash -. If this is the case then 2 things:

1) this is an Excel issue - Access wont provide any additional
functionality that is not already contained in Excel

2) If all you need to do is to convert 0 values to dashes - just change
column formats in the spreadsheet to Numeric. Excel will automatically
display 0 values with a dash. And edit the columns so that they have
borders. Just right click on a cell selection and click on Format. No
programming required for this.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Rich,

Thanks for your reply. 0's to -'s I can do.

What I don't know how to do it "move" the columns with the non-0
values "to the left", as indicated below.

Here's the way the spreadsheet looks:
Fred |0|0|0|3|9|7|0|0|5|9|
Barney |0|0|0|3|5|7|0|4|2|0|
Wilma |9|0|8|3|9|7|0|0|5|0|
Betty |1|0|8|3|9|7|0|0|0|0|


Here is what I need in a new or updated table:
Fred |3|9|7|5|9|-|-|-|-|-|
Barney |3|5|7|4|2|-|-|-|-|-|
Wilma |9|8|3|9|7|5|-|-|-|-|
Betty |1|8|3|9|7|-|-|-|-|-|


__________________________________________________


On Jan 16, 12:52*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:

I think I get it now. To automate this will require a little bit of
coding. First set up your spreadsheet by creating a similar set of data
directly adjacent to your current set: Ex -- in the spreadsheet you
currently have the following columns

A B C D E F G H I J k l M N O
Fred 0 0 0 3 9 7 0 0 5 9 | | | | | | ...

...

Starting at column M create the same borders as you have in the first
set and set the values all to 0 -- use the Accounting format if you want
- dashes in place of 0. Then in the Visual Basic Editor we will add a
subroutine that will copy only numbers in each row which are greater
than 0 to adjacent cells on the same row. In the Tools Menu goto Macro
to Visual Basic Editor. Now insert a code module in the project from
the Insert menu. Copy this routine into the module

Sub CopyNumbers()
Dim rng As Range, i As Integer, j As Integer, k As Integer
set rng = Range("B1:K10, M1:V10)
For i = 1 To rng.Areas(1).Rows.Count
k = 1
For j = 1 To rng.Areas(1).Columns.Count
If rng.Areas(1)(i, j) <> 0 Then
rng.Areas(2)(i, k) = rng.Areas(1)(i, j)
k = k + 1
End If
Next
Next
End Sub

To experiment with this code do this: In a blank sheet in the same
Excel File add some fake data starting at row1 "A1" as above. Add Fred
and some numbers with 0's starting at column B to column K. Then add
Barney on row2, Wilma row3, ... don't even bother with borders- we are
just testing out the routine. Add 10 rows of data because I have set my
range object to read 10 rows of data. Make sure to intersperse some 0's
in your 10 rows. What the routine above will do is to read the 10 rows
and copy only the non zero numbers to a 2nd range which will be adjacent
to the first set starting at column M.

Now go back to the Tools menu in your sheet and goto Macro then select
Macros. You will see the CopyNumbers macro in the list. It should be
highlighted. Click the Run button and watch what happens. All the
numbers which are greater than zero will get copied to the cells
starting at M. This routine can copy 10,000 rows the same way in a
matter of seconds. Way better than doing it by hand.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Thanks very much, Rich!

I'll try this first thing Tuesday.

Patrick

On Jan 16, 4:47*pm, Rich P <rpng... (AT) aol (DOT) com> wrote:

Rich -

This worked like a charm, and is now saving one of our HR people an
hour (and some worry and frustration) each time they do the process.

Thanks very much for your patience and generosity.

Patrick