Easiest way to update data in report monthly
Robbro
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.
Steve
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
san...@penn.com
"Robbro" <Rob...@discussions.microsoft.com> wrote in message
news:F753730C-84CF-4F8A...@microsoft.com...
Jeff Boyce
(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" <Rob...@discussions.microsoft.com> wrote in message
news:F753730C-84CF-4F8A...@microsoft.com...
Steve
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
san...@penn.com
"Robbro" <Rob...@discussions.microsoft.com> wrote in message
news:F753730C-84CF-4F8A...@microsoft.com...
StopThisAdvertising
"Steve" <notmy...@address.com> schreef in bericht news:OAPyjU2g...@TK2MSFTNGP02.phx.gbl...
> 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.
You don't get it $teve... you are so bloody stupid ???
--
Get lost $teve. Go away... far away....
Again... Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...
This newsgroup is meant for FREE help..
No-one wants you here... no-one needs you here...
OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 10.500 pageloads... it's a shame !!)
Arno R
John... Visio MVP
news:OAPyjU2g...@TK2MSFTNGP02.phx.gbl...
> 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
> san...@penn.com
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" <notmy...@address.com> 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
John W. Vinson
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/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
--
John W. Vinson [MVP]
Duane Hookom
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:
> .
>
Robbro
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:
> .
>
Gina Whipp
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" <Rob...@discussions.microsoft.com> wrote in message
news:B59AA941-0034-44B6...@microsoft.com...
Robbro
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:
> .
>
Jeff Boyce
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.
"Robbro" <Rob...@discussions.microsoft.com> wrote in message
news:D41D9AA1-3994-45CA...@microsoft.com...
Robbro
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.....