Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2010
    Posts
    20

    Unanswered: Are ACCESS reports flexible or not?

    Hello,

    I think I'm having a problem with a basic concept, and I'd appreciate some help as I feel a bit stuck in the development of my database.

    Background: My users need to be able to choose which fields (columns) will be in the report (their output).

    Translation into my "need to do" list for the development: I need to create a form that will take whatever fields the users choose and place them inside a select query, and build a report out of the query.

    Problem: As I understand it, reports in MS ACCESS (as opposed to queries) are not flexible In order to build a report, I have to specify the exact fields that will participate always in the report and can't put field1 only part of the time, and field2 only at other times.

    Am I wrong? If so, can anyone explain to me how to build a "flexible" report or better yet, give me an example database?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    "Easiest" way to do this is to have as many text boxes as you could ever need on your report. Set the visibility to false. On open, iterate the columns selected by the user, assign to the control source of each text box in turn and make them visible. For extra headaches you could size and position them too.

    There isn't enough information to be sure this would be for you, but whenever I have provided users the option to effectively build their own queries I exported the data to Excel rather than used a report. Often users wanted to interrogate the data rather than have a read only report. If that sounds like an option then there are examples in the code bank.

  3. #3
    Join Date
    Apr 2010
    Posts
    20
    I was asked specifically for a access report, so the export to excel part isn't relevent. Thanks.

  4. #4
    Join Date
    May 2010
    Posts
    601
    Pardon me for jumping in ...

    See if the attached example helps
    Attached Files Attached Files
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Apr 2010
    Posts
    20
    Hitech,

    It didn't help, as it didn't have a connection with what I asked (but I guess it's my fault for not explaining well, Thanks anyway for trying).

    The basic question (rephrased) is:

    Can a Access report hold variables or only permanent existing fields? As I understand, a report has to relay on exisiting fields from a table and cannot be flexible to have changing number of columns.

    For example,
    If I have a table with fields named: name, telphone, address, inventory1, inventory2,
    A report has to hold fields like " name, telephone, address, inventory1" no matter what they hold, and cannot hold " " name, telephone, address, inventory" with the inventory being a variable consisting of iventory 1 or inventory2, submitted to the user's choice.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Adnaket View Post
    Can a Access report hold variables or only permanent existing fields? As I understand, a report has to relay on exisiting fields from a table and cannot be flexible to have changing number of columns.

    For example,
    If I have a table with fields named: name, telphone, address, inventory1, inventory2,
    A report has to hold fields like " name, telephone, address, inventory1" no matter what they hold, and cannot hold " " name, telephone, address, inventory" with the inventory being a variable consisting of iventory 1 or inventory2, submitted to the user's choice.
    No - you understand wrong. You can do what you say. Go back to my first post - the Excel suggestion was the second suggestion. Note that I suggested the easiest method - you can also dynamically create textboxes but it is easier to have them created at design time and set their properties (control source, visibility etc) at run time.

    As an aside, if that is a genuine set of table columns then it sounds like you have a design issue and have violated first normal form. Hacky stuff like this is one of the consequences of a non-normalised database.
    Have a look here and particularly pay attention to repeating groups (ignore the qualifications about this - at the highest levels there is some controversy about repeating groups but unless you are a very experienced data modeller just assume they are bad): First normal form - Wikipedia, the free encyclopedia

  7. #7
    Join Date
    Apr 2010
    Posts
    20
    [QUOTE=pootle flump;6461892]No - you understand wrong. You can do what you say.

    Ok, thanks, pootle. I was about to go mad, as I knew it is possible but couldn't find examples...

    Do you have an example of the use of text boxes in a report? It would help me a lot as the few trys I had with the entering of combo boxes and text boxes into reports were catastrophic, to say the least, and I have nobody else to consult.

    As for the 1NF, the above was only an example as my database is a lot bigger. I read the description and it seems that my database fullfils 1NF (at least partly), but not 2NF. Anyways, I have a conceptual problem with it, as I understand it, but I don't understand how to translate 1NF/2NF databases into the simple form my users insist on:

    -------------------------------------------------
    | |
    | lblInventory1 : txtInventory1 |
    | |
    | lblInventory2 : txtInventory2 |
    |__________________________________________|

    (and so on). The txt's are supposed to be textboxes with amounts.

    Can you refer me to a example database that has a 1NF/2NF databases and does this simple thing? If not, do you know a site that has instructions how to do it? As my database is huge and was handed down to me by the previous employee (I didn't design it) I'm a bit nervous to do expirements on my own...

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - let's take this a step at a time.

    Since a report is read only (it's kind of like a PDF - it is only meant for reading) a combo box is not something you would ever add. textboxes and labels are the two controls you will use the most.

    Unless someone else can cut in then I'll have to leave this till lunch since it is likely to take a while. In the meantime, check out HTCs database. In particular look at the report in design view. You are especially interested in the Record Source property of the report and the Control Source properties of the text boxes. That is how to display data in a textbox. Note however we can change these properties in code which is how we change a report from static to dynamic.

  9. #9
    Join Date
    Apr 2010
    Posts
    20
    OK, Cool.

    I've read about the ControlSource Property and trialed with it a bit
    (the enclosed mdb has 2 examples: the first report replaces one column with anothor's data, and the second one does it based on a combobox value.
    the fisrt worked like a beuty but the second one didn't (and I can't figure out why, but I know that it's not the ControlSource's fault)).

    Is there more to it than the simple controlsource?
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Change the extension to mdb.

    Play around - shout if you don't understand anything. I only changed code, nothing else.
    Attached Files Attached Files

  11. #11
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Adnaket View Post
    Hitech,

    It didn't help, as it didn't have a connection with what I asked (but I guess it's my fault for not explaining well, Thanks anyway for trying).

    The basic question (rephrased) is:

    Can a Access report hold variables or only permanent existing fields? As I understand, a report has to relay on exisiting fields from a table and cannot be flexible to have changing number of columns.

    For example,
    If I have a table with fields named: name, telphone, address, inventory1, inventory2,
    A report has to hold fields like " name, telephone, address, inventory1" no matter what they hold, and cannot hold " " name, telephone, address, inventory" with the inventory being a variable consisting of iventory 1 or inventory2, submitted to the user's choice.
    I agree that it is possible.

    I have one report that I use for printing many different crosstab queries. In A crosstab query, then columns and the count can be different every time the report is run.

    I have this example that might help:

    Dynamic Report based on a Crosstab query
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  12. #12
    Join Date
    Apr 2010
    Posts
    20
    Pootle,

    Thanks a million, you solved a mystery I've been wondering about a lot. I have to give it a thought as to how to implement it into my database (and maybe check the control source behavior with multiple textboxes and labels), but I can already say it's a huge help(thanks for fixing the example code as well).

    As for the 1NF/2NF, do you have a example that I can learn, as I didn't understand it fully
    Quote Originally Posted by Adnaket

    I read the description and it seems that my database fullfils 1NF (at least partly), but not 2NF. Anyways, I have a conceptual problem with it, as I understand it, but I don't understand how to translate 1NF/2NF databases into the simple form my users insist on:

    -------------------------------------------------
    | |
    | lblInventory1 : txtInventory1 |
    | |
    | lblInventory2 : txtInventory2 |
    |__________________________________________|

    (and so on). The txt's are supposed to be textboxes with amounts.

    Can you refer me to a example database that has a 1NF/2NF databases and does this simple thing? If not, do you know a site that has instructions how to do it? As my database is huge and was handed down to me by the previous employee (I didn't design it) I'm a bit nervous to do expirements on my own...

    HighTech,

    Thanks you as well - I only peeked at your database but it seems very promising and I hope I'll have the time soon to study it thoroughly...

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is an excellent introduction to relational database design:
    The Relational Data Model, Normalisation and effective Database Design

    Access tempts you to think of database design in terms of forms and reports. Understand that this is wrong. Databases should be designed according to the business entities they represent. Report and form formats are simply known as presentation issues - your presentation layer and data layer should be as decoupled as the technology allows. In other words - design your table correctly according to database design principles and then worry about how your reports look.

    Anyway, in short I suggest you look at sub-reports.

  14. #14
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    Are ACCESS reports flexible or not

    You may take a look at the following link for an example of Cross-Tab Queries and Dynamic Report:

    LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Dynamic Report
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •