Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Make Table, Combine Three Queries of Data into the new table

    I'm going crazy with this for five days now.

    Here is one of the three queries . . .

    Code:
    INSERT INTO tblCARETSData ( StreetNumber, StreetName, City, ClosingDate, ListPrice, ClosePrice, ListAgentOfficeKey, SaleOfficeName, SaleAgentAgentID, SaleAgentLastName, County, PropertySubType, CommonWalls, ListingEntryDate, ListingDate, PendingDate, OffMarketDate, BuildingSize, OriginalListPrice, ListPriceLow, DOM, CDOM, YearBuilt, ZipCode, TaxParcelNumber, ListingID, UniqueID )
    SELECT TblCRMLS_CLOSED.StreetNumber, TblCRMLS_CLOSED.StreetName, TblCRMLS_CLOSED.City, TblCRMLS_CLOSED.DateClosedSale AS ClosingDate, TblCRMLS_CLOSED.ListPrice, TblCRMLS_CLOSED.SellingPrice AS ClosePrice, TblCRMLS_CLOSED.LO_Key AS ListAgentOfficeKey, TblCRMLS_CLOSED.LO_Name AS ListOfficeName, TblCRMLS_CLOSED.SO_Key AS SaleAgentOfficeKey, TblCRMLS_CLOSED.SO_Name AS SaleOfficeName, TblCRMLS_CLOSED.County, TblCRMLS_CLOSED.PropertySubType, TblCRMLS_CLOSED.CommonWalls, TblCRMLS_CLOSED.TimestampOriginalEntry AS ListingEntryDate, TblCRMLS_CLOSED.DateListingContract AS ListingDate, TblCRMLS_CLOSED.DatePurchaseContract AS PendingDate, TblCRMLS_CLOSED.DateOffMarket AS OffMarketDate, TblCRMLS_CLOSED.SquareFootageStructure AS BuildingSize, TblCRMLS_CLOSED.ListPriceOriginal AS OriginalListPrice, TblCRMLS_CLOSED.ListPriceLow, TblCRMLS_CLOSED.DOM, TblCRMLS_CLOSED.CDOM, TblCRMLS_CLOSED.YearBuilt, TblCRMLS_CLOSED.PostalCode AS Zipcode, TblCRMLS_CLOSED.APN AS TaxParcelNumber, TblCRMLS_CLOSED.MLnumber AS ListingID, TblCRMLS_CLOSED.ListingKey AS UniqueID
    FROM TblCRMLS_CLOSED;
    The new server uses different field headings so I need to change the field names in the queries to match the old field names so all of my programs will still function.

    This has not been an easy task for me.

    At first I had one of the three queries a "make table" query then the remaining two tables were APPEND queries.

    It worked for a while then I made some kind of change to try something new and I haven't been able to get it to work again.

    I have three separate downloads each to collect different data, however ALL of the fields are the same just that one of the downloads will have empty data that will insert from one of the other queries.

    Ideas . . ?

    Rick

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what isnt working
    What columns that you have renamed and forgotten to rename in this query
    how is anyone supposed ti be abke to diagnowe the problem


    lets be clear this isnt down to the new server magically changing column or table names, this is something you instigated, you made the concious choice to change something. You need to work through all forms, reports and queries to reflect those design changes.

    offhand there is no need to alias columns (the 'mycolumn as anothername') as the column name in a subquery has no meaning, but the column order does
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    offhand there is no need to alias columns (the 'mycolumn as anothername') as the column name in a subquery has no meaning, but the column order does

    This I believe is where my problem is but I don't quite understand it.

    I'm going to try your suggestion.

    Get all the data off the new server. This requires 3 separate downloads.

    After that I must combine the 3 downloads and then append them as one into the old table with the correct colume names and structure.

    Will this work?

    Rick

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    TblCRMLS_CLOSED.SellingPrice AS ClosePrice
    you don't refer to closeprice inthe query so it has no value, no meaning.

    so try removing the aliases and see if that makes a difference

    as said beofre you dont' actually need to use a query to do this assuming you have the columns in the same orders
    open the source table
    select all the rows
    copy all the rows to the paste buffer (CTRL + C)
    open the destination table
    paste all the rows fromt he paste buffer (CTRL + V)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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