Easiest way to update data in report monthly

  • Thread starter Thread starter Robbro
  • Start date Start date
R

Robbro

Got my report finally laid out with all necessary info on there for November.
Now I need to know the easiest way to update my info every month to generate
that months reports. I have 3 tables and 1 total query feeding into a query
that feeds into my report (I'm a total newb, may not have done this the most
effecient way). The relationships are all set up for NovSales table to
NovReturns table to NovVariances table to my NovTotalQuery. The data comes
to me in excel spreadsheets which I just imported to make the tables. I went
back and imported Octobers tables but really am clueless when it comes to
getting those #'s to flow through to my report without going back and
repeating most of the work. I'm betting there is a much easier way to do
this.
 
Presumably your data has a date field to identify what momth the data is
for. You can import your data each month to your tables accumulating data
for all months. Then in your query set up a criteria to limit the data
returned by your query for a desired month and your report will only show
the data for the month you selected.

Steve
(e-mail address removed)
 
It sounds like you are describing using three tables to hold November data
(Sales, Returns, Variances), then using three more tables for the next
month, and three more ...

If that's how your tables are set up, you don't have a relational database
(e.g., MS Access), you have a spreadsheet.

Even if the original data comes from Excel, you are making much more work
for both yourself and Access if you try to feed Access 'sheet data.

If the terms "normalization" and "relational" are not familiar, plan to
spend some time coming up to speed on them if you want to get the best use
of Access' relationally-oriented features/functions.

You've described a "how", as in how you are trying to do something.

If you'll describe a bit more specifically "what" you are trying to do,
folks here may be able to offer more specific suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Hello Robbro,

If you need help setting up a way to update your info every month to
generate reports for a selected month, I can help you. I provide fee-based
help for Access, Excel and Word applications. My fee to help you would be
very modest. Contact me if you want my help.

Steve
(e-mail address removed)
 
Steve said:
Hello Robbro,

If you need help setting up a way to update your info every month to
generate reports for a selected month, I can help you. I provide fee-based
help for Access, Excel and Word applications. My fee to help you would be
very modest. Contact me if you want my help.

Steve
(e-mail address removed)
Click to expand...



These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past Christmas
period and a few gems from the Access newsgroups to show Stevie's
"expertise".


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...


Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve



Sept 9, 2009
Steve said:
you can then return all the characters in front of it with the Left()
fumction. Would look like:
Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

Steve
Click to expand...

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.



Sept 22,2009
Sorry Steve, even I can see that this is a useless answer. I made it pretty
clear that "CW259" is just ONE possible value for the control.

Steve said:
Hello David,

Open your report in design view and select txtOrderID. Open properties and
go to the Data tab. Put the following expression in the Control Source
property:

=IIF([chkActive],"CW259","(CW259)")

Steve
Click to expand...


John... Visio MVP
 
The relationships are all set up for NovSales table to
NovReturns table to NovVariances table to my NovTotalQuery.
Click to expand...

STOP.

As Steve and Jeff say, you're "committing spreadsheet".

You need ONE sales table, with a date field for the date of sale.
You need ONE returns table, with a date field for the return date.
I'm not sure what's in the Variances table, but you should only have one of
them too.

Your report would be based on a query with a date criterion to retrieve only
those records for a particular month (or any other desired date range).

You're using a relational database, not a version of "Excel on steroids"!

Here are some resources to get you started with what will turn out to be a
different way of thinking about data:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Depending on the structures of the Sales, Returns and Variance tables, you
might even consider combining these into a single table with a field that
describes the transaction type.

If you can't or won't change the structure, consider creating a normalizing
union query to place all the records in a single results set.
 
Ill admit I'm in limbo, I'm beyond what excel can do for me easily but not
wanting to invest fully in setting up databases. I just want this report
without tons of hassle and formatting every month, thats what was killing me
with excel, pivot tables could not do what I wanted while a regular
spreadsheet could, but took hours of format/setting up every month.
So I really am wanting a spreadsheet on steroids I guess you could say. And
I'm excited that I'm nearly there with my report, I just fear I'm bumping
into either my admitttedly sever limitations of knowledge of databases or the
limits of Access itself.
Either way, it appears creating linked tables to 3 spreadsheets that I can
copy/paste all my data into will get me the report I need, and I have the
report format and calculations pretty much complete, its just slow and I'm
starting to create a lot of errors. Are linked databases that link to
spreadsheets going to cause slowness when working with the query and report
designs?
 
Robbro,

I think you're missing the main point here... Should you decide to invest
the time it will take to set up your database properly you will have your
report with minimal effort every month going forward. Access itself only
has the limitations of its setup. Setting up it like a spreadsheet on
steroids well it behaves badly and causes you all sorts of issues which you
are running into. To continue to use it the way you are using it... expect
slowness, difficulty in creating reports, constant formatting of reports and
inaccurate query results.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Ok, here is where I am:

I have set up a report on Novembers data which is nearly exactly what I
want. I have tried the following to try to make it work with October's data:

1. Linked tables, run the same query, use the same report. I would just
copy/paste my data into a fixed spreadsheet every month. This appears to
work great, I checked my query and it updates exactly as I expect, even when
the # of records is different and when the format of my input changes a
little (as long as my headings that are used in the query are correct in the
source files, im good to go, and the columns in my sources do change at
times, but the info I'm using is fairly stable, it may just move around). As
soon as I try to go to the report though I get overflow errors and can only
open it in design view which is SLUGGISH, as in 2-3 min between each mouse
click. My summary query is slow too but not nearly to this degree.

2. Import each months data into table, choosing the sheet: Big problem
here is when my source files change formatting (out of my control) it really
screws me up, plus it seems to want to name the empty columns to the right of
my data new field names which are not in my current table, so then I tried....

3 Import each months data into table by named range, which is just the
columns that my data are in. This also imports a bunch of empty rows at the
bottom that causes problem. When I go in and manually set a named range of
say a1 to z1000 or whatever I get closer, but if anyone changes the columns
in my input I get problems.

Overall I wish #1 would just work, and probably would for somone that knew
what they were doing. Tomorrow morning is my last chance to work on this
before month end again so I'll probably end up going back to the old
spreadsheet and formatting a few hours every month unless I can make a
breakthrough. Thanks for everyones suggestions though, I am learning a lot
doing this and it may be usefull later even if not on this project.
 
You've described "how" you are trying to use Access to do something ... but
don't seem to be hearing that folks are telling you the way you've done that
will cause you (and Access) a lot of work and headaches.

If you want to use a tool, doesn't it make sense to learn how that tool
works? After all, would you expect a chainsaw to drive nails ... <g>?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Ok, got #1 to work finally, a simple 0 where it should not have been that I
had deleted from the tables in my other attempts fixed almost everything.
I'm sure I'll hit other problems, but I'm learning.

Thanks again everyone!

And sorry for trying to fit a square peg into a round hole, but I only have
a square peg and a triangle peg.....
 
Back
Top