Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a process (from a legacy system) that runs monthly that provides month-end raw data. I import the data each month to a staging table, where I then have a query that rolls this data up. My query includes the following fields:

    Property
    Year
    Month
    Account
    Revenue
    Customers

    I also have a list in SharePoint where I move this data for reporting purposes (I have a linked table in Access to the SharePoint list). The list includes the same fields. My question is this... Each month, I delete all the data in SharePoint and then append ALL the data. Is there any way possible to compare the two sources and only append the new month-end data?

  2. #2
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried the following query, but it doesn't appear to work:

    SELECT qry_staging.Year, qry_staging.Month, qry_staging.Property, qry_staging.ID, qry_staging.Revenue, qry_staging.RoomNights
    FROM qry_staging LEFT JOIN qry_production ON (qry_staging.ID = qry_production.Account) AND (qry_staging.Property = qry_production.Property) AND (qry_staging.Month = qry_production.Month) AND (qry_staging.Year = qry_production.Year)
    WHERE (((qry_production.Property) Is Null) AND ((qry_production.Year) Is Null) AND ((qry_production.Month) Is Null) AND ((qry_production.Account) Is Null));

    The staging table is the main table that includes the new records. Property plus Year plus Month plus Account are the unique fields...

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I know nothing about Sharepoint, so I was reluctant to reply to this. Does the fact that Sharepoint is involved make any difference?

    You want to append to the production table any records from staging that are not already there? and the test for "being there" is a match for all 4 fields : Property, Year, Month and Account ?

    You say that the query "does not appear to work"...What happens? Does it return too many records ? not enough? or just the wrong ones?

    I presume you realise that a 'select' query is not going to append records? I agree that it would be a good strategy to get a select query working , then convert it to an append query. But I am just checking that you realise this step is needed.

    Can you post a sample db, with just the two tables, (with a little bit of dummy data) and your queries?
    Regards
    John



  4. #4
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,
    Thank you for your response. Correct, Property + Year + Month + ID (Account) makes a record unique. Yep, I'm trying to nail down the select query before moving on to the append. That said, I believe I have my issue down to a problem around Null values. I have the following record that is displaying in each table (staging and production), but continues to be pulled in my select query:

    Year Month Property ID RoomRevenue RoomNights
    2009 10 MISC $0.00 81

    The record should be excluded becuase it's already in production. Whenever I have a null value for the ID (Account field), the record displays, even though the record exists in production. The ID field is actually stored as a number.

Posting Permissions

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