Results 1 to 6 of 6

Thread: Append?

  1. #1
    Join Date
    Jun 2008
    Posts
    33

    Unanswered: Append?

    I have a new question similar to this topic: http://www.dbforums.com/showthread.p...15#post6351315 . (That one, I'm not worrying about anymore)

    So say I've got two tables, t1 and t2. Each one has the field "Departments". Then, regarding entries, they have many of the same ones, but some different ones, too. So let's say some reports I'm running look at queries that look at t1's "Departments", but I need an option to run the same reports for the "Departments" in t2. So I would append the departments from t2 into t1 with an append query, right?

    But what if there are some records that were under the department "Legal Action" in one table, but someone input records that are meant to be of that same kind ("Legal Action") at another time into another table under the department "Legal" in that other table. Now since the jobs tracked under "Legal Action" and "Legal" are meant to be the same thing, someone just neglected to be consistent with how they entered the jobs into the database. So let's say I wanted to run my append query on the table with "Legal", and put it in the same table as the one with "Legal Action", and also change my heading on all those jobs from "Legal" to "Legal Action", too. Does this require an Update query in addition to an Append query? If so, does that mean that anytime I run a report on "Legal Action" projects, this Append query and Update query needs to run every time?

  2. #2
    Join Date
    Jul 2008
    Posts
    9
    Can I start by answering a question with a question?

    If your T1 and T2 are so similar, why are they not combined into a single table of like data?

    Usually the only time I ever use an Append query is when I'm importing data from an external source and placing it in a pre-defined table structure. Once you append data to a table, it is now a permanant part of that table. If you append data from T1 to T2, now the same data exists in two places which is usually not a good idea.

    Absolutely you can use an Update query to make the stored data conform to a set standard. Then too, if you want the data to always conform, you can use pull-down boxes in forms to restrict the entries to pre-defined values. You can even place restrictions at the table level to prevent "illegal" entries.

    As far as re-using a report with two different sources of data, you can set up a macro in a menu form to do this. But it sure sounds to me like the tables should be merged if they are so similar. Duplication of data in similar tables is one thing most people try to avoid if possible. Some times it makes more sense to break the data into more than one table and then link key fields together where a one-to-many relationship exists.

    Bill

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can explicitly define the target for your "Append" queries, which are actuall "INSERT INTO" queries when you get down to the nitty gritty SQL.

    Eg:

    INSERT INTO t1 (Departments, Legal)
    SELECT departments, [Legal Action]
    FROM t2


    Similarly, you can union the tables together if you want to read them both as the same recordset:

    SELECT departments, legal AS [Legal Action]
    FROM t1
    UNION
    SELECT departments, legal
    FROM t2
    oh yeah... documentation... I have heard of that.

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

  4. #4
    Join Date
    Jun 2008
    Posts
    33
    Quote Originally Posted by Bill322
    Usually the only time I ever use an Append query is when I'm importing data from an external source and placing it in a pre-defined table structure. Once you append data to a table, it is now a permanant part of that table. If you append data from T1 to T2, now the same data exists in two places which is usually not a good idea.
    See that's the whole deal. One of the tables I am dealing with here is a linked table from SharePoint in which this Acess database pulls from. The whole issue is, the way some people have been defining/inputting jobs with the department "Legal" when it needs to be "Legal Action" (these aren't the literal names of what's in my database, but same idea). Then in addition to this, some of the queries look at the linked SharePoint table, and some look at the internal Access one. So data comes into the database by way of a setup with a linked SharePoint table, which I had nothing to do with structuring; so I'm a bit cautious and careful and not wanting to rock the boat too much, lest I destroy an entire department's ability to input jobs, or the other department's ability to report on said jobs. As it stands now, most of the reports that are run, department-by-department, turn out all right--but some don't. And this is one case that I've been requested to fix.

    Quote Originally Posted by Bill322
    Absolutely you can use an Update query to make the stored data conform to a set standard. Then too, if you want the data to always conform, you can use pull-down boxes in forms to restrict the entries to pre-defined values. You can even place restrictions at the table level to prevent "illegal" entries.

    As far as re-using a report with two different sources of data, you can set up a macro in a menu form to do this. But it sure sounds to me like the tables should be merged if they are so similar. Duplication of data in similar tables is one thing most people try to avoid if possible. Some times it makes more sense to break the data into more than one table and then link key fields together where a one-to-many relationship exists.
    So would this mean setting up something like this

    - set up a button on a form that displays the desired report
    - reports draws from a certain query
    - set up event such that when a user clicks the button on the form to open this report, it runs an Append query to grab all the similar data from the SharePoint linked table to the Access table, then runs an Update query to make sure all that data that was appended in the append query--incuding the department names--matches what the query is going to be searching for so that the results are included
    - report displays

    ?

    Quote Originally Posted by Teddy
    "You can explicitly define the target for your "Append" queries, which are actuall "INSERT INTO" queries when you get down to the nitty gritty SQL.

    Eg:

    INSERT INTO t1 (Departments, Legal)
    SELECT departments, [Legal Action]
    FROM t2


    Similarly, you can union the tables together if you want to read them both as the same recordset:

    SELECT departments, legal AS [Legal Action]
    FROM t1
    UNION
    SELECT departments, legal
    FROM t2"
    I'm fine with working with raw SQL like that, and those definitely sound like something I'd need--but I'm definitely NEW at working with the base SQL. So, where would those statement(s) go? New queries? Or the queries that are set up to run my report? Where inside of the queries'; under "Criteria" of a given field, or what?

    Thank you for your responses.
    Last edited by gsempcb; 08-06-08 at 09:53.

  5. #5
    Join Date
    Jul 2008
    Posts
    9
    gsempcb,

    Let me see if I understand your situation, please correct if wrong. You have database #1 in which one group of people input data and also database #2which another group of people input data. Then database #2 has a table link to database #1 and database #2 also holds the reports that need to be run combining data from both databases?

    If this is correct, then what Teddy said about setting up a Union Query would be a better approach. Now with a union query, each table source must have the same number of output fields and each field must be labeled the same. If the linked table has different field names but the data is equivelent, you can cast the field name to an Alias by using the "AS [field Name]" string. As an example, the following is a Union query I use to pull data from 4 different tables which each have different field names, but I cast them to common field names in the query.

    Code:
    SELECT
    Connectors.Noun_1,
    Connectors.Manufacturer AS MFR,
    Connectors.Con_PN AS [MFR P/N],
    Connectors.Company_PN AS [Local P/N],
    Connectors.Description AS Notes,
    Connectors.Con_PN
    FROM Connectors
    WHERE (((Connectors.Active) Like "*Yes*"));
    
    UNION ALL SELECT
    Terminals.Noun_1,
    Terminals.Manufacturer AS MFR,
    Assoc_Term.Term_PN AS [MFR P/N],
    Terminals.Company_PN AS [Local P/N],
    Terminals.Description AS Notes,
    Assoc_Term.Con_PN
    FROM Connectors INNER JOIN (Terminals INNER JOIN Assoc_Term ON Terminals.Term_PN = Assoc_Term.Term_PN) ON Connectors.Con_PN = Assoc_Term.Con_PN
    WHERE (((Connectors.Active) Like "*Yes*"));
    
    UNION ALL SELECT
    Locks.Noun_1,
    Locks.Manufacturer AS MFR,
    Assoc_Lock.Lock_PN AS [MFR P/N],
    Locks.Company_PN AS [Local P/N],
    Locks.Description AS Notes,
    Assoc_Lock.Con_PN
    FROM Locks INNER JOIN (Connectors INNER JOIN Assoc_Lock ON Connectors.Con_PN = Assoc_Lock.Con_PN) ON Locks.Lock_PN = Assoc_Lock.Lock_PN
    WHERE (((Connectors.Active) Like "*Yes*"));
    
    UNION ALL SELECT
    Seals.Noun_1,
    Seals.Manufacturer AS MFR,
    Assoc_Seal.Seal_PN AS [MFR P/N],
    Seals.Company_PN AS [Local P/N],
    Seals.Description AS Notes,
    Assoc_Seal.Con_PN
    FROM Seals INNER JOIN (Connectors INNER JOIN Assoc_Seal ON Connectors.Con_PN = Assoc_Seal.Con_PN) ON Seals.Seal_PN = Assoc_Seal.Seal_PN
    WHERE (((Connectors.Active) Like "*Yes*"))
    ORDER BY [Con_PN], [Noun_1];
    The easiest way I've found to build a union query is to make individual select queries, casting the field names to the common fields I want, then view the SQL code generated. Then I cut and paste the SQL from that query into my union query SQL editor to build up the Union Query. Just make sure any sorting "ORDER BY" only occurs on the very last item.

    This should give you a common data set to use in any report that you want to generate and pull data from both databases at the same time. This way you do not have data duplicated between the two databases.

    Hope this helps! Bill

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I wonder if it wouldn't be better to create a "lookup" field on your contentType within SharePoint to prevent user error?
    oh yeah... documentation... I have heard of that.

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

Posting Permissions

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