Subtotal/Summary with Data in Different Fields

[shaqattack1992-newsgroups]

Hello Everyone,

At work, employees have been using a spreadsheet that I am trying to
import into an access database to make some reports. The problem I'm
having is that in the spreadsheet, they are storing the same type of
information in multiple cells. For example, for each machine in the
shop, there is a code for why the machine was down--A1, B1, C1...for
example. The spreadsheet is set up like this:

Machine------------Code1----------Code2--------Code3
----------------------------------------------------------
Drill-----------------A1------------B1---------C1
Press--------------B2------------A1---------C2
Press2------------A1------------B2---------C1

There are slots for Code1, Code2, and Code3 just to give the user 3
slots to enter information. There is no order to it. I realise this
isn't a good way to do things, but I have hundreds of these
spreadsheets that I would like to import into a database and not have
to manipulate every one. I'm used to making a report that just groups
by a certain field so I can see all the code A1s, but A1 is in
different fields. Is there a way to group by A1 or any other code in
access. Any help or ideas would be appreciated.

Thanks,

-Chad

[roader55]

Instead of going sideways u can go downward

in other words u can design a table with fields

Machine , code and date

this way the user can actually enter more than 3 codes

When u design reports u can use a cross tab query or something like
that
Now to import data to the the table u can copy paste it or u can use
the access API

Search in microsoft.com for the documentation (VBA for ACCEss and
EXcel)
Any ways it goes something like this

1 U have to add a reference to MS Excel in access VBA editer( Tools>
reference)
2 Dim AppExcel as Excel.application
3 if u refer to the documentation u can find methods to manipulate the
data

have fun!!!



sh***********************@yahoo.com wrote:

Hello Everyone,

At work, employees have been using a spreadsheet that I am trying to
import into an access database to make some reports. The problem I'm
having is that in the spreadsheet, they are storing the same type of
information in multiple cells. For example, for each machine in the
shop, there is a code for why the machine was down--A1, B1, C1...for
example. The spreadsheet is set up like this:

Machine------------Code1----------Code2--------Code3
----------------------------------------------------------
Drill-----------------A1------------B1---------C1
Press--------------B2------------A1---------C2
Press2------------A1------------B2---------C1

There are slots for Code1, Code2, and Code3 just to give the user 3
slots to enter information. There is no order to it. I realise this
isn't a good way to do things, but I have hundreds of these
spreadsheets that I would like to import into a database and not have
to manipulate every one. I'm used to making a report that just groups
by a certain field so I can see all the code A1s, but A1 is in
different fields. Is there a way to group by A1 or any other code in
access. Any help or ideas would be appreciated.

Thanks,

-Chad

[kingston via AccessMonster.com]

Once you get the data into Access, perform a union query:

SELECT [Machine], [Code1], "Code1" FROM Table
UNION SELECT [Machine], [Code2], "Code2" FROM Table
UNION SELECT [Machine], [Code3], "Code3" FROM Table;

This will result in a long list with machines and codes. You'll be able to
filter across all machines and codes from this one list.

sh***********************@yahoo.com wrote:

>Hello Everyone,

At work, employees have been using a spreadsheet that I am trying to
import into an access database to make some reports. The problem I'm
having is that in the spreadsheet, they are storing the same type of
information in multiple cells. For example, for each machine in the
shop, there is a code for why the machine was down--A1, B1, C1...for
example. The spreadsheet is set up like this:

Machine------------Code1----------Code2--------Code3
----------------------------------------------------------
Drill-----------------A1------------B1---------C1
Press--------------B2------------A1---------C2
Press2------------A1------------B2---------C1

There are slots for Code1, Code2, and Code3 just to give the user 3
slots to enter information. There is no order to it. I realise this
isn't a good way to do things, but I have hundreds of these
spreadsheets that I would like to import into a database and not have
to manipulate every one. I'm used to making a report that just groups
by a certain field so I can see all the code A1s, but A1 is in
different fields. Is there a way to group by A1 or any other code in
access. Any help or ideas would be appreciated.

Thanks,

-Chad

--
Message posted via http://www.accessmonster.com

[shaqattack1992-newsgroups]

kingston via AccessMonster.com wrote:

Once you get the data into Access, perform a union query:

SELECT [Machine], [Code1], "Code1" FROM Table
UNION SELECT [Machine], [Code2], "Code2" FROM Table
UNION SELECT [Machine], [Code3], "Code3" FROM Table;

This will result in a long list with machines and codes. You'll be able to
filter across all machines and codes from this one list.

Thanks for the help!

-Chad