[Tasha wrote]
"I have a macro set up to import the text file to Excel, removes headers,
resaves under current date, then is imported into Access. "
[Tasha wrote] "I have two tables imported to Access. Both tables have same
column headings. They are both revenue reports that have line items of MTD
and YTD totals by gl no and service code, the report is run daily to get the
daily revenue from yesterday's end of day which I am currently using a
calculator to get.
GLNO|SERVCD|DESC|MTDIPQTY|MTDIPAMT|MTDOPQTY|MTDOPAMT|YTDIPQTY|YTDIPAMT|YTDOPQTY|YTDOPAMT
Each table is named by the date and time it was run : (this can be changed)
Table 1 061807-1214p
Table 2 061907-1217p
I repasted info I had previously replied regarding my tables.....I imported
them into Access because I couldn't think of a way to do what I needed in
Excel. The original report is a text file with headers and other garbage on
the page, so I created a macro to remove all the headers and footers, etc.,
then save as an Excel file and import to Access. In a nutshell, I have two
reports..... I need to take yesterday's report(table1), subtract quantity and
amount(which is a MONTH TO DATE total) from today's report(table2) to get
yesterday's DAILY total). I don't know how to do reports yet in Access, so
the end result should be an export to Excel so I can create a report with
headers, etc and send as e-mail attachment to my boss.....it should appear
exactly as I showed in my last reply, the same columns need to be in the
export table that are in table1 and table2. I don't know how else to explain
it. I thought it would not be that difficult, but as I said have been unable
to find a way to do it. Thanks for your help!
Klatuu said:
I think you are making it harder than it really is. You say you are
exporting it from Access to Excel, making changes, then importing back in to
Access? Why is that?
Where does the data come from? What table or tables is it in? How does it
get there? Are you wanting the output to be a report, a form, or an export
to Excel?
You are wanting today's totals, Month To Date, and Year to date?
Sorry for so many questions, but this is sounding more complex that you
originally posted. Not a problem, but is can be done.
--
Dave Hargis, Microsoft Access MVP
:
The table is a report that is run daily, however it is run with month to date
and year to date totals. We have no way to get daily total revenue by
service code other than to pull this report and to get the daily revenue to
subtract yesterdays totals from today's. I have a macro set up to import the
text file to Excel, removes headers, resaves under current date, then is
imported into Access. From there I don't know what to do with it. What I
need is a report of daily revenue by service code.....and the only way to get
this is to do as I mentioned above and subtract yesterday's totals from
today's as they are on the report as month to date. Here is a small example
from each report and what I am needing to get from them:
(from today's report)
GLNO SERVCD DESCRIPTION MTDIPQTY MTDIPAMT MTDOPQTY MTDOPAMT
YTDIPQTY YTDIPAMT etc...
011 70301 R&B 1 285.00 250230.00
-6 -5268.00 3366.00 2954788.00
131 70364 LAB-UA
1 57.00
(from yesterday's report)
GLNO SERVCD DESCRIPTION MTDIPQTY MTDIPAMT MTDOPQTY MTDOPAMT
YTDIPQTY YTDIPAMTetc...
011 70301 R&B 1 211 185258.00
-6 -5268.00 3292.00 2889816.00
{this report doesn't have a line item for LAB-UA as above}
(the results I need)
GLNO SERVCD DESCRIPTION IPQTY IPAMT OPQTY
MTDOPAMT YTDIPQTY YTDIPAMT etc...
011 70301 R&B 1 74.00 64972.00 -6
-5268.00 3366.00 2954788.00
131 70364 LAB-UA
1 57.00
Thanks for your help, I have spent hours researching to figure out how to
get this, but have nothing so far...
:
You question is not really clear. Can you please be more specific about the
business rules?
There is one glaring problem here. You should not use a different table for
each day. This will actually create more problems than it will solve. What
you should have is one table with a field that determines the date and time
of the run.
If you can describe your business rules a little more clearly and tell me
how you are doing the import, I can help make this work for you.
--
Dave Hargis, Microsoft Access MVP
:
I have two tables imported to Access. Both have same column headings.
GLNO|SERVCD|DESC|MTDIPQTY|MTDIPAMT|MTDOPQTY|MTDOPAMT|YTDIPQTY|YTDIPAMT|YTDOPQTY|YTDOPAMT
Each table is named by the date and time it was run :
Table 1 061807-1214p
Table 2 061907-1217p
These tables are imported daily. What I need to do is to take the
information from the most current table (would be today's date) and compare
it to the information in yesterday's table, giving me the same columns, but
with the difference of today's totals minus yesterday's totals. How would I
do this? I am not real fluent with Access yet. These tables may have
different data each day, such as one day it may have a service code on one
with that day's numbers, that isn't on the other, and I would need it to pick
that up as well.
