Break down table by rows

[Guest]

Hello. I have an import table which imports an Excel spreadsheet. What I need
to do it break this spreadsheet down into three separate parts, based on the
headings of the sections of the spreadsheet. The sheet is something like this:

Returned Transactions:
1 5436.43
2 71345.54
3 1634.12
etc.
Represented Transactions:
1. 6526.43
2. 65132.23
3. 6431.52
etc.
Non-Merchant Transactions:
1. etc., etc.

What I need to do is break out the Returned and Represented Transactions
portions of the sheet into separate tables, then cross-reference the
information in those tables to another imported data file. I already have the
cross reference portion of the database done, but I'm getting totals on the
whole sheet, which doesn't work because the Returned Transactions are debits
and the Represented Transactions are credits (to our company's bank), so I
can't give a sum of them without showing the Represented Transactions as
negative numbers. So, here are what I see my options as:

1. Somehow flag each record as either Represented or Returned in the
existing table, or
2. Cut the records to separate tables and work with them separately.

I don't need anything for the Non-Merchant transactions, we don't do
anything with those so I can just ignore that portion. Any ideas?

 

[Guest]

Nicholas,

I suggest you use the first of the two options you suggest. Add an integer
field to the Transactions table, TransactionType, a foreign key to a new
table:

TransactionTypes
TTID AutoNumber (Primary Key)
TT Text ("Represented", "Returned", "Non-Merchant"

During the import, either:

1. Add a column to the Excel spreadsheet that carries the type, and import
it with the rest of the data.
2. Name the three ranges, and import them separately, running an Update
query after each to add the appropriate TransactionType.

Hope that helps.
Sprinks

 

[Guest]

I'm not quite sure I understand your explanation. I understand the adding the
text column part, but what does that have to do with the primary key? And how
do you import ranges from the sheet if the ranges change each day? Do I need
to create the ranges in the spreadsheets first before I import it? If so,
then that defeats my purpose, which is to completely automate the process.

I'm not creating this database for myself, I'm creating it for another
coworker who does not have the Access and Excel knowledge to do everything
required without a lot of extra work. Rather than take her 2 hours to do it
all by hand, this database will let her do it all automatically, all she has
to do is pick the import files and it'll handle the rest (in an ideal world
).

 

[Guest]

Nicholas,

Maybe we need to back up to the structure of the Excel file. You said they
were broken up by a different heading in the spreadsheet. Don't these
heading lines become nonsense records on import? What I was suggesting was
turning your data (from your sample data in your original post, to:

Transaction Amount Transaction Type
5436.43 1
71345.54 1
1634.121 1
6526.43 2
65132.23 2
6431.52 2
etc., etc. 3

The second table, TransactionTypes, was to associate the code stored in the
Transactions table (1,2, or 3) with more meaningful text for displaying the
type on a form or report.

I fully understand your desire to accurately automate the process. You
didn't describe how the user inputs the data now, i.e, whether the three
headings are in a fixed location on the spreadsheet, or just anywhere, so
it's hard to advise you on the precise code, but I think that you could
easily write VBA code to operate on the raw Excel data to put it in the form
shown above.

The data could then be easily imported into Access into the *same* table,
but differentiated by the transaction type. A query could then calculate the
correct totals, using a calculated field something like:

TransValue: IIf([TransactionType]=1,[Transaction],
IIf([TransactionType]=2,-1*[Transaction],0))

Hope that helps.
Sprinks

 

[Guest]

No, the section headers are not in the same location each time, because the
number of items in the file I'm importing changes daily. I know this is most
likely going to be best handled with vB coding, but I don't have any
knowledge of how you would do this. I've tried working with recordsets
before, but have never had any success... and it appears that recordsets will
be the only way to go about this. So if anybody can give me some ideas, I
would greatly appreciate it. Thank you!

Nicholas,

Maybe we need to back up to the structure of the Excel file. You said they
were broken up by a different heading in the spreadsheet. Don't these
heading lines become nonsense records on import? What I was suggesting was
turning your data (from your sample data in your original post, to:

Transaction Amount Transaction Type
5436.43 1
71345.54 1
1634.121 1
6526.43 2
65132.23 2
6431.52 2
etc., etc. 3

The second table, TransactionTypes, was to associate the code stored in the
Transactions table (1,2, or 3) with more meaningful text for displaying the
type on a form or report.

I fully understand your desire to accurately automate the process. You
didn't describe how the user inputs the data now, i.e, whether the three
headings are in a fixed location on the spreadsheet, or just anywhere, so
it's hard to advise you on the precise code, but I think that you could
easily write VBA code to operate on the raw Excel data to put it in the form
shown above.

The data could then be easily imported into Access into the *same* table,
but differentiated by the transaction type. A query could then calculate the
correct totals, using a calculated field something like:

TransValue: IIf([TransactionType]=1,[Transaction],
IIf([TransactionType]=2,-1*[Transaction],0))

Hope that helps.
Sprinks

I'm not quite sure I understand your explanation. I understand the adding the
text column part, but what does that have to do with the primary key? And how
do you import ranges from the sheet if the ranges change each day? Do I need
to create the ranges in the spreadsheets first before I import it? If so,
then that defeats my purpose, which is to completely automate the process.


I'm not creating this database for myself, I'm creating it for another
coworker who does not have the Access and Excel knowledge to do everything
required without a lot of extra work. Rather than take her 2 hours to do it
all by hand, this database will let her do it all automatically, all she has
to do is pick the import files and it'll handle the rest (in an ideal world
).