Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to tell from which table a record comes from? (2002/SR-1)

    I created union query which allows the user to search the Active cases that are in tblMaster and the Closed Cases (or archived cases) in tblMasterArc. My search form consist of a continuous form that list the defendant's first name and last name from tblMaster and tblMasterArc. If the user selects an Active defendant's name, the form opens filtered to that defendant.
    Here is where I'm having trouble. If the user selects a defendant that has been archived, I want to open a report showing that records information instead of the form for Active cases.
    If anyone is still following me? My question is how can I tell from which table the defendant's name came from so that I can either open the form for Active cases or the report for Closed (Archived) cases.
    Thank you for any help with this.
    Don

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: How to tell from which table a record comes from? (2002/SR-1)

    I see a couple of problems here.

    1. How are defendents referenced, i.e., unique id? What happens if there are three John Smith entries (all different people) or John Smith (one person) has more then one entry. Are you linking on the Last Name, First Name and unique Case Number?

    2. Can a person be both active and archived? (I do not see why not...)

    3. Why not store all data in one table (eliminating the union query) and set a field that indicates the data is archived. You could then search for the archived field, or later date, or other to indicate the data is archived.


    To answer you question, using a union query, you will be unable to determine which table the data came from unless you make the field names represent the table. I would suggest adding an archive field and using one table to eliminate the union query.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to tell from which table a record comes from? (2002/SR-1)

    Well I just solved my problem, probably not pretty but it works. I created a dummy field called ArchiveDate in the table tblMaster. This will field will not be used or populated by anything, just used in the Union query. If it's null then the record is in the Active table tblMaster if it's not null it has to come from the Archived table tblMasterArc.
    Not pretty but works nicely!
    Anyone want to tell me why I should not do this? I can't think of any reason I would run into trouble.
    Thanks for your help Gary.

    Don

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: How to tell from which table a record comes from? (2002/SR-1)

    You said <<The archive table tblMasterArc includes a unique field in it that is not included in the active table tblMaster. This field is ArchiveDate. If I could include this field in with the union query then I could tell which table the defendant comes from, because if the defendant is archived then ArchiveDate will not be null. But I'm not sure how to show all defendants Active and Archived and include this field ArchiveDate? >>

    Why not make ArchiveDate a field in the active table and always leave it as a null value or set it to a dummy date that you know is not real. You could then use the archive date in the union query as well and get what you are after - Null values or the dummy value is current and other values are archived.

    HTH
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How to tell from which table a record comes from? (2002/SR-1)

    Sorry to have come in so late, but there is an easier way to do this.
    Simply introduce an additional field in both selects of the union query, eg.

    Say your union query looked like:
    SELECT field1, field2, field3 FROM tblMaster WHERE .. blah blah
    UNION ALL
    SELECT field1, field2, field3 FROM tblMasterArc WHERE .. blah blah

    Simply change it to:
    SELECT "M" as RecID, field1, field2, field3 FROM tblMaster WHERE .. blah blah
    UNION ALL
    SELECT "A" as RecID, field1, field2, field3 FROM tblMasterArc WHERE .. blah blah

    You can then simply test the contents of RecID to determine what to do next.

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to tell from which table a record comes from? (2002/SR-1)

    Pat, thanks for the great tip! Works great and easier than hell!

    Don

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to tell from which table a record comes from? (2002/SR-1)

    Thanks for the quick response Gary!

    <font color=red>1. How are defendants referenced, i.e., unique id? What happens if there are three John Smith entries (all different people) or John Smith (one person) has more then one entry. Are you linking on the Last Name, First Name and unique Case Number?</font color=red>
    Yes, I am linking by a unique Case Number, and I failed to mention that this number is in the continuous form before the defendant first and last name.

    <font color=red>2. Can a person be both active and archived? (I do not see why not...)</font color=red>
    Yes, John Smith could have 2 records one that closed a year ago and one that is active now, but they are unique in the list because of the Case Number. If John Smith shows in the list twice then the user can look at the Case Number to differentiate between them.

    <font color=red>3. Why not store all data in one table (eliminating the union query) and set a field that indicates the data is archived. You could then search for the archived field, or later date, or other to indicate the data is archived.</font color=red>
    I started off with having all the data in one table which is very easy to work with, but there are a ton of cases and the table would get bloated. I send the closed cases off to archive to speed up the overall use of the database.

    The archive table tblMasterArc includes a unique field in it that is not included in the active table tblMaster. This field is ArchiveDate. If I could include this field in with the union query then I could tell which table the defendant comes from, because if the defendant is archived then ArchiveDate will not be null. But I'm not sure how to show all defendants Active and Archived and include this field ArchiveDate?
    Thanks for any further help!
    Don

    Edited by Charlotte - sorry Don, but the Snap font was virtually illegible on my screen so I hanged it to italic

Posting Permissions

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