Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Confusion (Access 2K, Win 2KPro, SQL Server 2K)

    This is one of those times where I am having trouble figuring out how to get what's in my head into an Access db design. Perhaps the loungers can help -- or tell me what I'm trying to do is not doable.

    OK, I have a system that pulls data from SQL server for some info with several additional tables/forms/fields for *doing* stuff to the SQL Info.

    All is working OK except I made a strange design choice early-on and now need to re-tool some stuff.

    Basically, I need to pull a tab out of a form setup for treating recieved information into a separate form for tracking mailouts on those who have not sent in requested info. I have several fields that are NOT on the source queries. Simple question: can you add fields in a query that don't exist in the tables being used to pull the info? I know you can add calculated fields, but I need to add some other fields as well.

    If this doesn't make sense let me know. It doesn't really make sense to me as I type it...

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

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

    Re: Access Confusion (Access 2K, Win 2KPro, SQL Server 2K)

    <hr>It doesn't really make sense to me as I type it...<hr>
    I'm with you at that point, but you lost me in the preceding paragraph. You can't pull data out of fields that don't exist in the source tables of a query.

    Presumably the data is available in other tables which are not in the query at the moment. In that case, either add those tables to the select statement or use DLookup() to get the data if that doesn't affect performance too much. If the data isn't in tables, then you either have to invent it, or create tables which contain it, or ... maybe a random function?
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Confusion (Access 2K, Win 2KPro, SQL Server 2K)

    thanks for the response.

    Here's a little more detail on the table structure:
    1. A table listing all records that have been submitted via the webform
    2. A table that, thru some programming, copies that info into another table that has the required fields in the table structure for the tracking I want to do

    The problem is, I need to set up tracking on the info from table 1 that isn't part of table 2 (table 2 should hold only info that pertains to a recieved packet, therefore mailout tracking to get the packet is irrelevent).

    I can set up a query that finds all the info on table 2 that isn't on table 1, and even pop it into a new table via MakeTable query and re-create the desired fields so to build the desired form. However, if the info changes, how does the new table get adjusted to reflect a new population of records fitting the criteria setup in the maketable query? I guess, now that I am typing this thru, that that would be an UPDATE. However, I don't want the user to have to run an UPDATE, but have that happen 'behind the scenes' and preferably any time a change occurs (ie doing a run query on db open isn't a great solution). This is why I was fantasizing that you can just add columns to a query resultset as that would, if it were possible, be a solution to the problem.

    DLookup() might work, tho... and, in this case, probably won't be such a drag on performance. But those are for populating info into one form from another, and that's not what I want either. Well well well... I need to keep working this all out. My mind is starting to click onto something, perhaps the 'something' will surface as I keep going.

    If this still makes no (or just a little) sense, let me know...

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

    Re: Access Confusion (Access 2K, Win 2KPro, SQL Server 2K)

    Have you considered the possibility of putting a trigger on the primary table that would automatically add a record to the secondary table if the primary record contains certain data? I think that might be the simplest way to do what you are trying to accomplish, and then you wouldn't need to do the secondary Access processing. Triggers can be used with the INSERT event in SQL Server.

    BTW, I'm traveling now, and it will likely be a week or so before I have Internet access again. Sorry.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Confusion (Access 2K, Win 2KPro, SQL Server 2K)

    nah, that's too boroque. but i finally figured it out. I'll add the tracking columns to the table (SQL Server), re-hook it into Access if the new columns don't show up and build the tracking form off of a query.

    Sheesh! Why was I having this problem?? You know I was assuming that, since you can 'add' columns to a source table in a query by putting in calculation fields, I thought you could just tack on new columns in the query. Well, that would be an odd feature as queries don't (AFAIK) allow you to setup datatypes, etc. Thus the train of thought led me to try something that is not in the design set of a RDBM. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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