Easiest way to update data in report monthly

Discussion in 'Microsoft Access Getting Started' started by Robbro, Dec 22, 2009.

  1. Robbro

    Robbro Guest

    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.
     
    Robbro, Dec 22, 2009
    #1
    1. Advertisements

  2. Robbro

    Steve Guest

    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



    "Robbro" <> wrote in message
    news:...
    > 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.
     
    Steve, Dec 22, 2009
    #2
    1. Advertisements

  3. Robbro

    Jeff Boyce Guest

    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" <> wrote in message
    news:...
    > 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.
     
    Jeff Boyce, Dec 22, 2009
    #3
  4. Robbro

    Steve Guest

    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




    "Robbro" <> wrote in message
    news:...
    > 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.
     
    Steve, Dec 23, 2009
    #4
  5. Re: Easiest way to update data in report monthly -

    "Steve" <> wrote in message
    news:...
    > 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
    >




    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" <> 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... Visio MVP, Dec 23, 2009
    #5
  6. On Tue, 22 Dec 2009 13:49:01 -0800, Robbro <>
    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]
     
    John W. Vinson, Dec 23, 2009
    #6
  7. Robbro

    Duane Hookom Guest

    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 <>
    > 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, Dec 23, 2009
    #7
  8. Robbro

    Robbro Guest

    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 <>
    > 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]
    > .
    >
     
    Robbro, Dec 23, 2009
    #8
  9. Robbro

    Gina Whipp Guest

    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" <> wrote in message
    news:...
    > 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
    >> <>
    >> 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]
    >> .
    >>
     
    Gina Whipp, Dec 23, 2009
    #9
  10. Robbro

    Robbro Guest

    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" <> wrote in message
    > news:...
    > > 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
    > >> <>
    > >> 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]
    > >> .
    > >>

    >
    >
    > .
    >
     
    Robbro, Dec 23, 2009
    #10
  11. Robbro

    Jeff Boyce Guest

    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.

    "Robbro" <> wrote in message
    news:D...
    > 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" <> wrote in message
    >> news:...
    >> > 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
    >> >> <>
    >> >> 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]
    >> >> .
    >> >>

    >>
    >>
    >> .
    >>
     
    Jeff Boyce, Dec 23, 2009
    #11
  12. Robbro

    Robbro Guest

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

    "Robbro" wrote:

    > 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" <> wrote in message
    > > news:...
    > > > 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
    > > >> <>
    > > >> 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]
    > > >> .
    > > >>

    > >
    > >
    > > .
    > >
     
    Robbro, Dec 23, 2009
    #12
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. New to Access

    What is easiest way to update BE linked table using forms?

    New to Access, May 19, 2005, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    177
  2. Guest

    I.m new can anyone tell me the easiest way to learn?

    Guest, Jan 3, 2006, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    157
  3. Guest

    easiest way to use CMS documentation? Word 03

    Guest, Feb 2, 2006, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    168
    John Vinson
    Feb 2, 2006
  4. Guest

    What is the easiest way to learn Excel?

    Guest, Aug 16, 2006, in forum: Microsoft Access Getting Started
    Replies:
    3
    Views:
    145
    Guest
    Aug 17, 2006
  5. cowichandave

    Looking for Easiest Way to Create Report

    cowichandave, Dec 3, 2009, in forum: Microsoft Access Getting Started
    Replies:
    9
    Views:
    319
    Philip Herlihy
    Dec 4, 2009
Loading...

Share This Page