1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2003

Discussion in 'Business Applications' started by chrisb1978march, Jan 18, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. chrisb1978march

    chrisb1978march Thread Starter

    Joined:
    Sep 20, 2007
    Messages:
    185
    Hello all,

    I have a problem with pulling some reports off my database, The report is taking a name, a date and then specific information on an accomodation issue. Because of the way I have set up my database (i'm a beginner, and self taught) all the information is on one table, so there'll be other fields on the table The table has a name, date and then specific information based on their accomodation. Because the date is specific to the whole page and not just a field (i didn't know how to do that) when I run the report it's showing every date whether there is information relating to the accomodation or not. But it isn't displaying the other information in it as it doesn't relate to accomodation.

    Is there anyway I can hide the dates with no information in them? I can apply a filter in the query i have and it hides everything without information in it, so it hides the dates. But I can't then set up a report on the filtered information.

    I'm sure I have completely confused you... but if anyone has an idea of what i'm saying, your help will be greatly appreciated. I can try and make this clearer too

    Thanks in advance
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, you appear to have half solved your problem with the filtered query.
    Why can't you get a report based on that query to work?
     
  3. chrisb1978march

    chrisb1978march Thread Starter

    Joined:
    Sep 20, 2007
    Messages:
    185
    Ah ha it does seem to have worked now! :)

    Also is there anyway to run a report that lets a user input a name to extract information only for that person? I know I can sort the criteria on a query but then i'd need to do it for all the names I have in the database, and I want to make it as easy as I can for others to use.

    Thanks in advance
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The easiest way to do what you want is to have a form with a Combo box to select a Name using the Record ID and the name. Set the record ID column width to 0cms.
    Then in the query that supplies your Report you add this to the RecordID column's first Criteria Row.
    forms![Formname]![Comboname]

    where Formname is the actual name of your form and Comboname is the actual name of your combo box.
     
  5. chrisb1978march

    chrisb1978march Thread Starter

    Joined:
    Sep 20, 2007
    Messages:
    185
    Thanks OBP that worked :)

    Can anyone help me with this question? I have 3 tables which are linked. The first being Personal Details which records names and addresses etc. The second one is Offending History where it gives details of offences that a person has committed, that has an indeterminate relationship to the table personal Details and is linked via the name, on a one to many basis. I then have another table called Further Information which has an indeterminate relationship to Offending history table via the name again on a one to many basis again.

    At the moment users are having to type the offenders name three times and obviously for it to match records it needs to be exact. On the other 2nd and 3rd tables is it possible to use a combo box or something where the names are so they can just select whichever name using the personal details table as the reference? Obviously it will also have to maintain the relationship so it only pulls records for a particular name.

    Does this makes sense? Thanks you for you help in advance
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, the table relationships are not correctly set up for optimum design. They should be based Personal details Key ID Field (Autonumber).
    Plus I am not sure why the Further Information table is linked to the Offending History table via the name, it should either be related to the Personal details table or if it pertains to the Offense then it should relate to the Offending History table's Key ID field.
    With that structure you can use Subforms to automatically populate the Linking fields.
     
  7. chrisb1978march

    chrisb1978march Thread Starter

    Joined:
    Sep 20, 2007
    Messages:
    185
    Ah okay I will have a look into this and see which is the best option, thank you.

    Also if i'm running a parameter query which asks the user to enter a name, is it possible to either enter a specific name or get the query to list all of the names?

    Thank you
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, let me know if you need help with the Relationships.
    If you have Parameter query it only returns the one entered in to the parameter, unless you use the Like function in the parameter criteria.
     
  9. chrisb1978march

    chrisb1978march Thread Starter

    Joined:
    Sep 20, 2007
    Messages:
    185
    OKay I will do thanks for your help on this OBP, as I said i'm a beginner when it comes to Access and it's all stuff I have taught myself by trying things. So it's good that there's forums like this and like you around to help :)
     
  10. chrisb1978march

    chrisb1978march Thread Starter

    Joined:
    Sep 20, 2007
    Messages:
    185
    Oh I am going through reports now and was wondering if it is possible to do the reports so that they automatically leave out any fields with blanks in them. The query that the reports comes from has a date of entry and then has 12 other fields that may or may not have information in them. Now when the report is ran it shows all the fields even those with blanks in them. I know I can use a filter but it'll get rid all the other information as well if I use it to remove blanks.
    Can this be done at all?

    Oh and the 'Is Not Null' function in the criteria line on the query doesn't work either as that removes all of the information if I have it in each of the fields :(
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    It is possible, but it is quite a bit of work, especially if you want to move the other fields to hide the blank spaces all together. I am not sur it is worth the effort, most people realise that blank fields mean missing data and sometimes it is useful to prompt them for the data. Whereas if there is nothing there at all users do not necessarily know that it is missing.
     
  12. chrisb1978march

    chrisb1978march Thread Starter

    Joined:
    Sep 20, 2007
    Messages:
    185
    Okay I have now changed the layout of my database based on your advice here OBP and it seems to be a lot more organised! :)

    So thanks for your work and advice!
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975392

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice