PC Review


Reply
Thread Tools Rate Thread

Easiest way to update data in report monthly

 
 
Robbro
Guest
Posts: n/a
 
      22nd Dec 2009
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.
 
Reply With Quote
 
 
 
 
Steve
Guest
Posts: n/a
 
      22nd Dec 2009
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 Removed)


"Robbro" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
 
 
 
Jeff Boyce
Guest
Posts: n/a
 
      23rd Dec 2009
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.

"Robbro" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
Steve
Guest
Posts: n/a
 
      23rd Dec 2009
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 Removed)



"Robbro" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
John... Visio MVP
Guest
Posts: n/a
 
      23rd Dec 2009
"Steve" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 Removed)




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)

Steve wrote:
> Yes, you are right but a database is the correct tool to use not a
> spreadsheet.



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" <(E-Mail Removed)> wrote in message
> 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


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" wrote:

> 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



John... Visio MVP

 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      23rd Dec 2009
On Tue, 22 Dec 2009 13:49:01 -0800, Robbro <(E-Mail Removed)>
wrote:

>The relationships are all set up for NovSales table to
>NovReturns table to NovVariances table to my NovTotalQuery.


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/acc...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

--

John W. Vinson [MVP]
 
Reply With Quote
 
Duane Hookom
Guest
Posts: n/a
 
      23rd Dec 2009
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.

--
Duane Hookom
Microsoft Access MVP


"John W. Vinson" wrote:

> On Tue, 22 Dec 2009 13:49:01 -0800, Robbro <(E-Mail Removed)>
> wrote:
>
> >The relationships are all set up for NovSales table to
> >NovReturns table to NovVariances table to my NovTotalQuery.

>
> 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/acc...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
>
> --
>
> John W. Vinson [MVP]
> .
>

 
Reply With Quote
 
Robbro
Guest
Posts: n/a
 
      23rd Dec 2009
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?

"John W. Vinson" wrote:

> On Tue, 22 Dec 2009 13:49:01 -0800, Robbro <(E-Mail Removed)>
> wrote:
>
> >The relationships are all set up for NovSales table to
> >NovReturns table to NovVariances table to my NovTotalQuery.

>
> 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/acc...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
>
> --
>
> John W. Vinson [MVP]
> .
>

 
Reply With Quote
 
Gina Whipp
Guest
Posts: n/a
 
      23rd Dec 2009
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

"Robbro" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
> "John W. Vinson" wrote:
>
>> On Tue, 22 Dec 2009 13:49:01 -0800, Robbro
>> <(E-Mail Removed)>
>> wrote:
>>
>> >The relationships are all set up for NovSales table to
>> >NovReturns table to NovVariances table to my NovTotalQuery.

>>
>> 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/acc...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
>>
>> --
>>
>> John W. Vinson [MVP]
>> .
>>



 
Reply With Quote
 
Robbro
Guest
Posts: n/a
 
      23rd Dec 2009
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.

"Gina Whipp" wrote:

> 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
>
> "Robbro" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?
> >
> > "John W. Vinson" wrote:
> >
> >> On Tue, 22 Dec 2009 13:49:01 -0800, Robbro
> >> <(E-Mail Removed)>
> >> wrote:
> >>
> >> >The relationships are all set up for NovSales table to
> >> >NovReturns table to NovVariances table to my NovTotalQuery.
> >>
> >> 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/acc...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
> >>
> >> --
> >>
> >> John W. Vinson [MVP]
> >> .
> >>

>
>
> .
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
easiest way to create gridlines in an Access 2003 report Liam0364 Microsoft Access Reports 1 7th Jun 2010 07:39 AM
Looking for Easiest Way to Create Report cowichandave Microsoft Access Getting Started 9 4th Dec 2009 03:26 PM
Easiest way to extract end-of-day data from throughout-the-day data? sherifffruitfly Microsoft Excel Discussion 0 17th Sep 2007 09:19 PM
Easiest way to get 3rd col = monthly averages, from cols 1, 2 = weekly date, data? sherifffruitfly Microsoft Excel Discussion 3 1st Feb 2007 09:25 PM
What program is the easiest to make an annual monthly caolendar w. =?Utf-8?B?NTI0MCBCdXJuYWJ5?= Microsoft Powerpoint 2 12th Nov 2004 06:23 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:21 AM.