Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2011
    Posts
    5

    Question Unanswered: Returning what table information is pulled from???

    I have 5 tables with random information (names, phone numbers, ...)

    All 5 tables have a client name field because each table is for a client being seen for a different issue. Hence each table has different fields to accommodate the issue being seen.

    I have a union query setup to pull all names, dates, and the attorney seen from all tables that is generated for a report.

    I need to also display the table the name was pulled from in the report but have unsuccessfully been able to do so.

    I have tried the msysobjects.name sql function
    (SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE MSysObjects.Type=1 AND MSysObjects.Name Not Like "Msys*")
    but it will end up displaying a specific name and saying that that name was seen in all tables when that is not correct.

    How can I fix this or set up something to display what I am requesting? Thank you for your help!

    *I suppose I could combine the tables into one table, but I would like to not re-invent the wheel at this time since thousands upon thousands of records have already been entered into each table, so this is not an option...

    -Jason

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why would that not be an option? It's not like you have to manually move records.

    I think you should give more consideration to combining tables so you have fewer problems in the future, particularly if you're dealing with thousands and thousands of records.

    If you insist on running what sounds like an excel setup, you could include a literal string and call it SourceTable or something. IE:

    SELECT [someField], [anotherField], "FirstSourceTable" As SourceName
    FROM FirstSourceTable
    UNION
    SELECT [someField], [anotherField], "SecondSourceTable"
    FROM SecondSourceTable


    That database structure is highly likely to cause you a lot of pain though, fair warning.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2011
    Posts
    5
    Thanks Teddy,

    I won't be able to try your suggestion until tomorrow so I will respond accordingly.

    In response to why combining the multiple tables is not an option:
    When I think of the phrase "combining tables", I think of eliminating the 5 tables and just making one massive table. That would require me to add a field (reason being seen) to all forms, queries, reports, and tables (easy), then go back and manually enter in the nature of the issue the client is being seen for in all records so that I would be able to sort (extremely time-consuming and not what I would like to do).

    In the scheme of things, I am "new" to access, but have managed to create this elaborate (my own opinion) database from scratch (I'm very proud of it). If you have suggestions as to how to improve my database in any way possible like combining tables (if you could explain more in depth), I would greatly value your input.

    To give you more of an idea of how my database is setup besides actually looking at it from your own computer screen:
    Everything is initiated from the switchboard. The switchboard has radio buttons to click on what the client is coming in for (5 separate issues to choose from and a 6th button to search all tables for clients). Each separate submenu has an enter client information selection button, search button (for that particular issue), and a report button.
    *I did enter a lot of code for form specific data eg. Capitalize name after entry and so on. Other than that, everything is pretty basic I suppose.

    Everything works perfectly as far as I can tell but I don't want to have issues with it in the long run and I would like to improve it if at all possible...

    Thanks.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you want to post your relationship diagram we can help you figure out ways to tweak the design to be easier to work with.

    regarding this:
    In response to why combining the multiple tables is not an option:
    When I think of the phrase "combining tables", I think of eliminating the 5 tables and just making one massive table. That would require me to add a field (reason being seen) to all forms, queries, reports, and tables (easy), then go back and manually enter in the nature of the issue the client is being seen for in all records so that I would be able to sort (extremely time-consuming and not what I would like to do).
    Based on what you've said so far, I think you could use a client table, a visit table, and an issue type table at a bare minimum. I don't think you would have to manually enter in the nature of the issue for a given client. When it comes time to populate your "visit" table with records from your 5 duplicate tables, you can specify an IssueType to be assigned to that record based on what table it's coming from.

    That's just a guess though. I can't say for sure without knowing what your tables actually look like.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2011
    Posts
    5
    I'm attaching a snapshot of my relationship diagram... If there's another way to show the diagram, I'm unsure of the way. I do not have any relationships set up as I didn't think that I needed any.
    Attached Thumbnails Attached Thumbnails Relationship diagram.jpg  

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    theres no polite way of saying this, but bad design will always bite you in the ass

    take the time to do it properly, fix the problem and move on.

    even if you get a workaround for now it will still be an issue next time.

    fixing the problem could be
    merge identical/similar tables into one with an ID indicating the type or row that contains and / or user the sub/supertype approach to tablr design
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by jasonbs View Post
    I do not have any relationships set up as I didn't think that I needed any.
    Before you do any more designing, I STRONGLY urge you work through an Access book or class. All the way through. You're setting yourself up for more pain than you can imagine with the way you have things structured right now.

    Have a go with this in the mean time:

    Fundamentals of Relational Database Design -- r937.com
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Mar 2011
    Posts
    5
    Thanks Teddy,

    Yes, I'd really like to take a class so that I can learn all the "in's and out's" of Access.

    Right now, unfortunately due to time constraints and since I'm in the Military and I'm only doing this for the office I work with, I don't really have the time to take such a class. I have given a lot of time and effort to this project and I'm pretty happy with the fact that it does actually work for the most part considering the fact I've done this from scratch.

    I appreciate your time that you have given to look at this and assist me.

    -Jason

  9. #9
    Join Date
    Apr 2011
    Posts
    23
    I was asking a very similar question about structure and a member posted a link in my thread which explains how to combine and how to simply plan out your database from the start (Really easy to follow )

    http://www.dbforums.com/microsoft-ac...re-advice.html

    Found it very helpful.
    Last edited by Random Invert; 04-07-11 at 17:28.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by jasonbs View Post
    Thanks Teddy,

    Yes, I'd really like to take a class so that I can learn all the "in's and out's" of Access.

    Right now, unfortunately due to time constraints and since I'm in the Military and I'm only doing this for the office I work with, I don't really have the time to take such a class. I have given a lot of time and effort to this project and I'm pretty happy with the fact that it does actually work for the most part considering the fact I've done this from scratch.

    I appreciate your time that you have given to look at this and assist me.

    -Jason
    You can blow through the basics you need with a book and a weekend. It will take less time and effort to accomplish future tasks if you invest some time now. You're basically using Access as if it were Excel. For something that complex, that approach often ends in tears.

    Where are you anywho? I have a brother in law in army intelligence who gets shafted with random excel/access work too.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Mar 2011
    Posts
    5
    I'm stationed at wonderful (sarcastically) Ft. Stewart/Hunter AAF in Georgia...

    Sounds like your brother-in-law is in a good job field with lots of potential for post Military careers. I wish him best of luck!

    I'll look at getting a book this week and trying my luck at tackling it this next weekend.

    Thanks again buddy.

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
  •