Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2015
    Posts
    5

    Answered: Access 2007 trouble with Report – Is my db too complicated?

    Hi! I am new to this forum but have been sent here several times when searching answers to questions, so first off “Thanks!” for the help I received even before joining. I have had so many issues with this db that I am now at the point of asking the BIG question: have I made it just too complicated to work?

    I have been surprised that I can’t find sample databases to learn from. It’s been my experience that most agencies/businesses keep some basic demographic data (age, race, gender, disability, etc.) on their clients/customers/consumers. Of course, I’m working in Access 2007 because my non-profit clients cannot afford anything more up-to-date, so many of the official sample databases will no longer open. Also, most sample dbs focus on items sold rather than activities or services performed/provided.

    So I am building a database in Access 2007 (my original training years ago was in Access 2003). It is to be used for the reporting of client demographic and service data. In addition to the complications thrown at us by the reporting authority who keeps changing its mind about what and how to report, my User has added other complications. First complication, it will also be used to track information needed for other reports and/or other uses. Second complication, it will be used to track all clients, not just the ones who “qualify” for the main Report (I’ll call this main Report “V”).

    I’m on “version 11” now and have 7 tables, 2 of which are tied to Combo Boxes. My Entry Form has 2 Tabs and 6 Subforms. The reason for extra Subforms is that Access kept telling me I had too many controls on my Form, so I broke everything apart and made sure each Form is tied to a Table, not a Query.

    This is my main area of concern – that Access keeps acting like I have too much data or it is too complicated for the wizards. The Entry Form has 71 fields not including the Access Key ID, but 46 of them are simply check boxes. Of the others: 8 are text boxes for explanations of why something was/was not checked, 9 are independent text boxes, 4 are dates and 4 are Combo Boxes.

    Of these, obviously all are considered necessary by my User, but for the V Report it is: 23 checkboxes, 3 dependent text boxes, 1 Combo Box with 4 choices and a formula in a Query I built that assigns the clients to one of 3 age ranges (all this from 5 Tables and 1 Query). I have considered putting all the V data in 1 Table (except for the Age Range Query), but this would require completely re-working the Entry Form, which currently has the fields in the order my User wants them.

    So, the V Report I’m trying to build needs mostly to show the sums of the checkboxes, the sums of the 4 combo box choices, and the sums of the 3 age ranges – grouped into 8 sections with totals for each – and then the actual text entered (BUT only for “qualified” clients during specific date ranges).

    I have created Queries to turn each group of checkboxes, as well as the 7 other choices/ranges, into 1’s and 0’s (instead of the -1's and 0's Access makes them) and then sum them. I have created a Query to determine which clients “qualify” and added the sums from the other Queries to make this a Master Query that can be used to run the V Report whenever necessary. But the Report Wizard is overloaded when I try to use it with this Query. I have gotten it to work by splitting this into 3 separate “Master” Queries. This gives me 3 V Reports – the size of which shows that all data reported would easily fit on 1 page.

    Not only do I not think my User will be impressed with having to run 3 V Reports each time, but this does not include the text (which I can simply make a 4th report for, but there is never very much entered in these fields, maybe 6 entries for 100 records) – and I still have not addressed how to choose a Date Range for the V Report each time it is run. I’ve had trouble adding Date Range Queries to even simple Reports and am almost afraid to try it with this one.

    At this point I’m certain either that I am doing things all wrong, or that Access 2007 is not built to handle much! Any advice would be appreciated!!!

  2. Best Answer
    Posted by healdem

    "a picture (or two, or more) is worth a thousand words...
    ..especially when trying to understand data models and forms/reports

    bear in mind there is (or at least was) a hard limit of less that 255 columns in an Access query (certain up to A 2003 and quite probably since)

    a report, like a form can include embedded reports, so if your report contains more than 250 columns then consider splitting it into 2 and embedding those two sub reports into a single master top level report.

    what i often find is that organisations have a standard set of reporting criteria, but want to be able to modify or tweak as required. SO I tend to use a form to allow users to specify the criteria and then pull those criteria into the reports (and or forms as required (either as parameters on the openreport / openform arguments.. see the help for the parameter specification.

    because Im a bitter warped twisted individual with trust issues (especially in relation [I was going to use with respect to but no way does that 'r' word apply to most users ..] to users I actually deploy the parameter form as two items (the top level form holds the current default values, users can then either call reports/forms from parameter form OR change parameters in another form. the reason being that I know the top level form will wake up / first display known sane values (eg last financial period) and users can only change by going to another form so they can run the entire report suite using the same parameters without risk of you as application support getting phone calls claiming the financial period mysteriously changed between report or we cant do comparatives because report A has different data to report B. using a sub form to change parameters means the only way the data changed is because muppet central (the users) changed the parameters, and it CANNOT be inadvertent as its in a sub form...."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a picture (or two, or more) is worth a thousand words...
    ..especially when trying to understand data models and forms/reports

    bear in mind there is (or at least was) a hard limit of less that 255 columns in an Access query (certain up to A 2003 and quite probably since)

    a report, like a form can include embedded reports, so if your report contains more than 250 columns then consider splitting it into 2 and embedding those two sub reports into a single master top level report.

    what i often find is that organisations have a standard set of reporting criteria, but want to be able to modify or tweak as required. SO I tend to use a form to allow users to specify the criteria and then pull those criteria into the reports (and or forms as required (either as parameters on the openreport / openform arguments.. see the help for the parameter specification.

    because Im a bitter warped twisted individual with trust issues (especially in relation [I was going to use with respect to but no way does that 'r' word apply to most users ..] to users I actually deploy the parameter form as two items (the top level form holds the current default values, users can then either call reports/forms from parameter form OR change parameters in another form. the reason being that I know the top level form will wake up / first display known sane values (eg last financial period) and users can only change by going to another form so they can run the entire report suite using the same parameters without risk of you as application support getting phone calls claiming the financial period mysteriously changed between report or we cant do comparatives because report A has different data to report B. using a sub form to change parameters means the only way the data changed is because muppet central (the users) changed the parameters, and it CANNOT be inadvertent as its in a sub form....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Nov 2015
    Posts
    5

    sorta worked, but now my Query is MISSING?!

    Thanks for your quick reply. I love your attitude. I feel like I’ve met a kindred spirit, albeit a much more knowledgeable and experienced one, which is perfect!

    You've given me a much needed reality check which confirms what I believed, even if it alone does not fix the issues, at least now I don't feel so alone or (quite so) crazy.

    There are only 44 columns in the original Master Query, so quite a bit less than 255. This is why I’m going crazy. I don’t think what I’m asking of Access 2007 is at all excessive, so either there’s something wrong with Access or something wrong with the way I’m asking…

    HEY, IT DID IT! I used that old “trick” from the early days of Internet, “if you’re waiting for a page to load, go get a cup of coffee” (sounds ridiculous nowadays – no one can drink that much coffee!), but it worked! I ignored the “Not Responding” and left the little circle spinning and can say it took a full 5 minutes for the Report Wizard to work…

    ARRRRRRRRRRRRRRRRRGH!!!! I had just put the finishing touches on the Report, which took a while and everything went just fine, then when I hit Print Preview it said my Query did not exist?!?! And now, it doesn’t! I can’t find it anywhere!!!

    Sure, I can rebuild it, again, but wtf???!!!!!

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You may have a corruption in the db.
    Consider doing a compact and repair, but make certain you have goog copies BEFORE.
    Since changing to W10 ive gound Access unusable in design mode.... the properties box forces frequent repaints... ans can take 10 seconds or more between selection of cintrol and ability to do anything... not good.

    Always smart to save anything PRIOR to preview...
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Nov 2015
    Posts
    5

    So far, so good…

    Thanks for telling me! I’ve been putting off the upgrade to W10 just out of my own trust issues. But, yeah, I’ve had those slow reactions, too. You move a control and wait to see if it ended up where you thought you were putting it.

    Okay, I saved it as version 12, compact-and-repaired, analysed the Tables, Forms, Queries and Reports and followed their suggestions (even the one saying “Relate Table tbl_A to Table tbl_A.” - I wouldn't know how to do this, but told it to go ahead) and then…

    I rebuilt the new Master Query (again). Basically, I now have the “background” Queries that turn my Yes/No’s and others into 1’s and 0’s; then I have the Query that determines if a record meets the criteria and pulls the 1’s and 0’s from only those records; then I have the new Master Query that pulls only the 1’s and 0’s that met the criteria and sums them each for my V Report – which now all fits on 1 page!!!!

    (I figured out the reason I had split it into 3 sums Queries was because the Query Wizard would only allow me to choose “sum” on about a third of my data, so this time I built it myself all in 1 Query.)

    So far, so good. Now should I risk a sub-Report of the text that explains the “other” checks, or just put them on a separate Report? Hmmmmm…

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    my issues with the W10 upgrade may just be relevent to my own workstation, Ive seen nothing that indicates its a problem affecting pothers. I'm not doing a lot of Access development these days so its on the list "of when I get round to it...."

    it could be a contention with Visual Studio. I seem to remember one time in a code window the system wanted to re0install visual studio

    in a relational database I'd argue that ALL tables (that have related data) should have relationships defined at tabel level. thios relationships should enforce relational integrity, and probably update cascade. you need to take a concious decsion whether delete cascade is sensible for that use.

    having ad hoc joins performed when writing queries is a sure fire way of getting data chaos. using relations forces more rigour on the data
    https://www.google.co.uk/webhp?sourc...inks+in+access

    if you have columns with checkboxes you can use IIF to reprocess the data BEFORe doing any summarising eg:
    Code:
    select my, comma, separated, data, 
      iif(gender = "M",1,0) as IsMale,
      iif(gender = "F",1,0) as IsFemale,
      iif(gender <> "M" and gender <> "F",1,0) as UnknownGender,
      iif(mycheckbox = vbtrue, 1,0) as checkbox1True,
      iif(mycheckbox = vbfalse, 1,0) as checkbox1False,
    from mytable
    ..then you can summarise using that query as the feed stock. say that query is saved as myquery
    eg
    Code:
    select sum(IsMale) as NoMales, sum(IsFemale) as NoFemales, sum(UnknownGender) as NoSpecifiedGender, sum(checkbox1True) as Nocheckbox1True, sum(checkbox1False) as Nocheckbox1True from myquery
    bear inm mind that you are limited to around 255 columns (I think the query engine has/had a hard limit of 255 references and will deduct references for indexes and others, so plan on a maximum of around 250 columns in a query
    as far as the SQL engine is concerned the data source for the query (the from... bit) could be a table, a query a view..


    try to move away from the query designer. its fine for some tasks but like a lot of GUI front ends its limited, easily confused and in my books comes between the developer and the data. use raw SQL where possible. SQL is a very very powerful tool for extracting and manipulating data. its what the query designer writes behind the scenes.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    Nov 2015
    Posts
    5

    Good info…do you know how to force “one-to-many” in Access 2007?

    Lots of good info, thanks! As to the table relationship, it was suggesting I relate a table to itself. ( : I kinda assumed it already had a relationship with itself, though I can’t assume it was a good one. ( ;

    I do enforce referential integrity, and sometimes cascade update – I’m afraid of cascade delete because I’m not going to be the end-user. I’m also confused in 2007 (learned on 2003) that I can’t tell it I want a one-to-many relationship – it decides, and sometimes does one-to-one (or “indeterminate”) when it doesn’t make sense to me.

    Yes, you’re right, I do rely on short cuts like the Wizards too much. (I even fell into the trap of using Multi-Value Fields on my first build – what a nightmare!!)

    I think I lack confidence in my “coding” abilities, but have figured out a lot of complex IIf‘s – most of which have worked. The IIf has become my best friend in building this (it’s how I “change” the -1’s and 0’s to +1’s and 0’s). I find it funny that I’m still relying on concepts I learned on Lotus 1-2-3 a millennium ago (the If-Then function in writing macros).

    I’ll work on building my SQL confidence. Thanks for your “mentorship.”

  9. #8
    Join Date
    Nov 2015
    Posts
    5

    Figured out “one-to-many” – but why is Relationships creating a second version of a T

    Okay, I figured out that my 2 Tables that were tied to Combo Boxes needed to have the joined field set to “Indexed= Yes (no duplicates)” in order for it to be one-to-many.

    But a strange thing is happening. My main Table “tbl_Client_Records” is being repeated in the Relationship view. It is being named “tbl_Client_Records_1” and each time I get rid of it (Hide Table, then “save changes to layout”) it comes back when I open Relationships again. But, the table doesn’t actually exist anywhere in my db. It duplicates relationships to 4 of my other Tables, but does not duplicate the relationships to the 2 Tables tied to Combo Boxes.

    What is happening, and is it a problem?

    Including a pic this time, but “edited.” Though the “client data” in the db is faked (since they won’t let me take it off-site) there is enough reference to what they would consider private information i the field names that I’m uncomfortable posting it publicly online without some “redaction.” ( ;

    Click image for larger version. 

Name:	Relationship question.png 
Views:	5 
Size:	92.5 KB 
ID:	16632

  10. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats the way the relationships designer works if you have more than one jey/realtionship between the same pair of tables. so its not an issue, providing you do actually need more than one column in the 'child' table referring to a column in the 'parent' table. in these cases when presenting the relationship screen, what I do is make the duplicated table definitions the same size, then cover the _1,_2 defintiions with unsuffixed version and then jobsagoodun... you don't see the other tables, yet the relationship lines match with the columns they refer to.


    why would you have a self refernecing realtionship, well its quite commoin for hierarchies

    eg
    table: persons
    ID
    forename
    surname
    ...etc
    supervised_by

    supervised_by is nullable and refers to the ID in the same table. logically you's probably want to put a constraint that says the ID cannot be the same as supervised_by as you wouldn't want to model someone who notionally is managed by themselves.
    its nullable because people without an assigned supervised_by are either the top level OR dont' have a supervisor assigned.
    this approach falls down if say a person has more than one supervisor (in that case you'd need an intersection table to model a many to many relationship

    in a realtional database such as MS Access you need good reasons to have non realtional links between tables

    a comment
    are clients able to define themselves as more than one category. even if they are you may be better off having a table for race and an intersection table which allows a client to be associated with more than one race. granted given that in the real world (as opposed to the political / PC / public sector world many people have more than one racial origin.

    the other appraoch is to have a table for racial types and use that as the foreign key in client records. the reason, well you are njo lonmger dealign with hordes of checkboxes/boolean values. but the real power behind this approach is if say muppet central decides there needs to be a new racial classification OR decides that say one of the current category names needs changing (say from Black African American to just African American) or decides to add caucasian. heck you can actually be a smarty pants ask how people describe themselves but have soem form of marker indicating what each racial label translates to when you reprot to muppet central. eg:-

    say you needed to group all caucasian for reproting purposes into one category then you might have a racial origin table simialr to

    tabel: racial_origins
    ID 'autogenerated PK
    Description 'string / texst
    ParentID ' refers to ID in this table
    you coudl then have
    123 | caucasian | null
    163 | Anglo saxon | 123
    801 | American Irish | 123
    100 | Black American | null
    110 | Caribbean American | 100
    987 | African American | 100
    ..and so on. that way round you present the clioent with a loist of options and it doenst'[ ma,tter if they describe themselves as white serbo-croat (assumign that is an option yuou have defined, but when it comes to reproting you knwo that you can roll up that row as say caucasian
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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