Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combined info on a report (access2000)

    I volunteered to keep track of all the special dates for each month and post them to a widows mailing list-- I have the query running fine-- it extracts all the persons who have an anniversary or birthday or spouses birthday or the anniversary of the death of the loved one-- in a particular month (and since they dont list the year had to get around that-- but after the query returns the people with "hits" I can not get the data to appear in the report with out also listing all the "special dates" for each of these for the whole year. Ideally, I would be able to pull up all of the folks who would be having a tough month and needed extra attention but that it would only list for that person the actual events for that month. If you have any good Ideas; I would appreciate that. I sure hope I explained this well enough -- Thank you Ginger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combined info on a report (access2000)

    Can you give som more details about the query and about the table or tables the query is based on?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combined info on a report (access2000)

    sorry, I was so tired last night that I read the post about 6 times to make it just make that much sense. Ok-- all the info is in one table. Name, Birthday,Spouses Birthday, Anniversary, Name of Loved One, Date of Death, State (where the widow/widower lives) The query uses all the fields except the State (I kept that info just in case down the road the was some sort of get together and we wanted to notify only the people in that state) I use the first number in the date for the month (in june --6*) because it is not actually a date field-- they did not want to give the year in the dates. ) and use the OR criteria to get all the June birthday and all the June anniversaries etc. This works well and I get all the right data-- BUT when I do the report, it lists all the dates for all the people who have ANY June dates-- Barbara may have a June birthday-- and I want that listed; but I dont want listed in June that her husband had a birthday in March. I don't think there is a way to do this logically, but I would be nice. I tried to pull up four different queries and somehow get them all into one report; but was told it couldn't do that. If there is a work around for this, I would be pleased to know about it-- you saved me on the last project I needed help with.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: combined info on a report (access2000)

    Hi Ginger,
    I think your problem has more to do with table structure than anything else. If you used two tables where one had very basic information about the person (i.e. Name and Home Address), and a second table which had information about the specific event (i.e. Date, Type of Event, Name of Loved One) and a link to the first table, it would be much easier to pick out just those events for a specific person for a specific month. If you want to post a simple version of your database with some sample data, we could try to make your current structure work, but you might be better off restructuring things in the long run.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combined info on a report (access2000)

    If I understand correctly, your query returns ONLY records for dates in June, but the report shows more records. Thaat would mean that the Record Source of the report is not the query itself. Sorry to be a nuisance, but can you check that? You can try if setting the Record Source to the name of the query makes it do what you want. If not, post back with details about the Record Source of the report.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combined info on a report (access2000)

    You may be right-- I was thinking since this was going to be such a simple set of records; that I didn't need to divide the table-- there are no fields where there is duplication. Still; I can try that. Will check in later with the results of trying that-- it seems that it will still give me all the other special dates for anyone that has a date in June-- unless you mean that I should have a different table for EVERY type of date.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: combined info on a report (access2000)

    <hr>unless you mean that I should have a different table for EVERY type of date.<hr>
    No - just a single table - the KindOfDate is stored in each record. You could have a lookup table that listed the text description of all of the different kinds of dates - could be SpouseBirthday, SpouseDateOfDeath, ChildBirthday, etc....
    To limit it to just the special dates for a given person in a given month, in your query or data source you place criteria on both the person ID# and the month.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combined info on a report (access2000)

    no-- that is not the case-- the correct records and only the correct records are being returned. The problem is that I want to post all the monthly dates for each field and NOT post the dates from those same fields if they are not in the current month (would have been easier to work on this if we had actually used dates--but still would not fix this problem. Let me give you an example: Lets say that Mary has the following info in her record in the table [Mary; birthday 6/12; spouse birthday 5/11; anniversary 12/5/65; spouses name Larry; date of death 7/10/01; state California ] and
    [David ; birthday 11/23; Spouses birthday 9/21; anniversary 6/22/80; Spouses name Diane; date of death 6/ 23; state Ohio] Now ,I would like the report to say that Mary has a birthday on the 12th and that David has an anniversary on 6/22 and that his wife Diane died on 6/23--- I dont want to list Diane's Sept birthday or Mary's December anniversary. I hope I did a better job explaining this time.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combined info on a report (access2000)

    You'll need to follow Wendell's suggestion and put the dates in a separate table in order to easily achieve what you want. That will improve your design because there is no built-in imit to the number of types of dates you can capture and you can easily filter those dates on a report . If the values are strings rather than true dates, you may need to use the CDate function to convert them to dates in the query so that they can be filtered by month. On a form, you can use a subform based on the dates table to display a continuous list of dates for that person, with a dropdown for selecting the type of date.
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combined info on a report (access2000)

    not wanting to appear stupid here-- but I am not sure what you have in mind for the second table-- Table 1 would have the name and email address of each person-- email can be the key field since it is already unique-- but the table with dates-- cant get it in my head how that should look. the link and a field that says {Birthday 6/12) So each record in that table would have to repeat the "info" part-- I am sure you are not saying that as it would mean reapeating the same information in data entry. So sorry I am not "getting it" today. It feels like there needs to be tables for each kind of date.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combined info on a report (access2000)

    Your Table1 should have a unique key in addition to the person's name and email address. That can be an autonumber, which will take care of creating itself when you create a new record. Table2 (dates) would contain a copy of that unique key, plus the date type and date fields. You would "relate" the two tables on the unique key from Table1, which would NOT be unique in Table2, since you can have numerous dates for a single person from Table1. In a query, if you dropped both Table1 and Table2 on the grid, you would drag the unique key from Table1 to the same field in Table 2 to create a join between the two tables. Then you could display all the Table2 records for each record in Table1. Does that make it any clearer?
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combined info on a report (access2000)

    GOT IT !! Thank you Charlotte and all-- honest, I used to have a brain-- it is so frustrating not to be able to think clearly. But I am told it will all come back one day. In the meantime, thank you.

  13. #13
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combined info on a report (access2000)

    Just as a quick note to all of you who were so helpful-- the database is now working correctly and returning the information that I wanted-- I have not used CDate and will look that up to understand what it does as it would be nice if I could get the "dates" to come up in order-- rather than having 6/1 followed by 6/10 followed by 6/2 (yes, I know why it does that) LOL Thank you.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: combined info on a report (access2000)

    Either CDate or DateSerial could be use to make your dates sort correctly.
    Wendell

Posting Permissions

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