Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    13

    Angry Unanswered: Too many records in union query

    I have one query which returns 374 records and one that returns 15 records. I copied and pasted the SQL from these two queries into a union query and it returns 546 records, but it should really be 389? If it were fewer records, I would understand. The "extra" records I find in the union query are correct as far as my criteria. So why would the same exact SQL return different records just because it's in a union query? Any ideas?

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    The only thing that I can think of is that you dont have a group by. Other than that I have no Idea.
    Jim

  3. #3
    Join Date
    Oct 2002
    Posts
    48

    I hope, no. of columns and their order will be same.

    You may post here query too.

    Distinct is good option to make them stopping repeating.

  4. #4
    Join Date
    Nov 2002
    Posts
    13

    SQL

    It's rather lengthy, but here goes. I've also tried saving both these selects as separate queries and then using those queries in a union query, same effect. FYI, the extra records I'm finding are distinct, there aren't any duplicates in the results of the query, so I don't think it's as simple as a group. Any insight is appreciated.

    SELECT AP_hardware_type.description AS Type, AP_asset.equipment_id AS [Bar Code], AP_asset.serial_number AS [Serial Number], IIf(IsNull([desk_number]),[previous_loc],[desk_number]) AS [From Desk], AP_asset.location_code AS [To], IIf([survey_date]>[loc_transfer_date] Or IsNull([loc_transfer_date]),[survey_date],[loc_transfer_date]) AS [Transfer Date]
    FROM ((AP_asset INNER JOIN AP_asset_model ON AP_asset.model_code = AP_asset_model.model_code) INNER JOIN AP_hardware_type ON AP_asset_model.hardware_type_code = AP_hardware_type.hardware_type_code) LEFT JOIN dbo_pc_equipment ON AP_asset.serial_number = dbo_pc_equipment.serial_number
    WHERE (((AP_hardware_type.description)<>"HAND HELD" And (AP_hardware_type.description)<>"MONITOR SWITCH BOX") AND ((AP_asset.location_code) Like "74S*" Or (AP_asset.location_code) Like "39P*" Or (AP_asset.location_code) Like "70H*" Or (AP_asset.location_code) Like "12S*") AND ((IIf(IsNull([desk_number]),Null,IIf(InStr(9,[desk_number]," "),Left([desk_number],InStr(9,[desk_number]," ")-1),[desk_number]))) Is Null Or (IIf(IsNull([desk_number]),Null,IIf(InStr(9,[desk_number]," "),Left([desk_number],InStr(9,[desk_number]," ")-1),[desk_number])))<>[location_code]) AND ((AP_asset.loc_transfer_date)>DateAdd("d",(SELECT Max([AP To TCS Macro Last Run].[Macro Last Run]) FROM [AP To TCS Macro Last Run]),-7))) OR (((AP_hardware_type.description)<>"LAPTOP" And (AP_hardware_type.description)<>"HAND HELD" And (AP_hardware_type.description)<>"MONITOR SWITCH BOX") AND ((IIf(IsNull([desk_number]),[previous_loc],[desk_number])) Like "74S*" Or (IIf(IsNull([desk_number]),[previous_loc],[desk_number])) Like "39P*" Or (IIf(IsNull([desk_number]),[previous_loc],[desk_number])) Like "70H*" Or (IIf(IsNull([desk_number]),[previous_loc],[desk_number])) Like "12S*") AND ((AP_asset.location_code)="N/K") AND ((AP_asset.loc_transfer_date)>DateAdd("d",(SELECT Max([AP To TCS Macro Last Run].[Macro Last Run]) FROM [AP To TCS Macro Last Run]),-7))) OR (((AP_hardware_type.description)<>"HAND HELD" And (AP_hardware_type.description)<>"MONITOR SWITCH BOX") AND ((AP_asset.location_code) Like "74S*" Or (AP_asset.location_code) Like "39P*" Or (AP_asset.location_code) Like "70H*" Or (AP_asset.location_code) Like "12S*") AND ((IIf(IsNull([desk_number]),Null,IIf(InStr(9,[desk_number]," "),Left([desk_number],InStr(9,[desk_number]," ")-1),[desk_number]))) Is Null Or (IIf(IsNull([desk_number]),Null,IIf(InStr(9,[desk_number]," "),Left([desk_number],InStr(9,[desk_number]," ")-1),[desk_number])))<>[location_code]) AND ((AP_asset.loc_transfer_date) Is Null) AND ((AP_asset.survey_date)>DateAdd("d",(SELECT Max([AP To TCS Macro Last Run].[Macro Last Run]) FROM [AP To TCS Macro Last Run]),-7))) OR (((AP_hardware_type.description)<>"LAPTOP" And (AP_hardware_type.description)<>"HAND HELD" And (AP_hardware_type.description)<>"MONITOR SWITCH BOX") AND ((IIf(IsNull([desk_number]),[previous_loc],[desk_number])) Like "74S*" Or (IIf(IsNull([desk_number]),[previous_loc],[desk_number])) Like "39P*" Or (IIf(IsNull([desk_number]),[previous_loc],[desk_number])) Like "70H*" Or (IIf(IsNull([desk_number]),[previous_loc],[desk_number])) Like "12S*") AND ((AP_asset.location_code)="N/K") AND ((AP_asset.loc_transfer_date) Is Null) AND ((AP_asset.survey_date)>DateAdd("d",(SELECT Max([AP To TCS Macro Last Run].[Macro Last Run]) FROM [AP To TCS Macro Last Run]),-7)))

    UNION SELECT dbo_tcs_code_table.code_table_description AS Type, dbo_pc_equipment.bar_code AS [Bar Code], dbo_pc_equipment.serial_number AS [Serial Number], dbo_pc_equipment.desk_number AS [From Desk], [location] & "-" & [p_l] AS [To], Now() AS [Transfer Date]
    FROM (((dbo_pc_equipment LEFT JOIN AP_asset ON dbo_pc_equipment.serial_number = AP_asset.serial_number) INNER JOIN [Locations Surveyed] ON dbo_pc_equipment.desk_number = [Locations Surveyed].location_code) INNER JOIN dbo_tcs_code_table ON dbo_pc_equipment.type = dbo_tcs_code_table.code_table_value) INNER JOIN dbo_desk ON dbo_pc_equipment.desk_number = dbo_desk.desk_number
    WHERE (((dbo_tcs_code_table.code_table_description)<>"LA PTOP") AND ((dbo_pc_equipment.bar_code) Is Null) AND ((dbo_pc_equipment.desk_number) Not Like "*HUD*") AND (([Locations Surveyed].survey_date)>DateAdd("d",(SELECT Max([AP To TCS Macro Last Run].[Macro Last Run]) FROM [AP To TCS Macro Last Run]),-7)) AND ((dbo_tcs_code_table.code_table_type)="PC_TYPE") AND ((AP_asset.serial_number) Is Null));

  5. #5
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    If you can put your DB as a ZIP File, prob it would be better to understand

    Mean while try to use a select query on this Uniqon query and use a sort and DISTINCT. It might help.

    Just a suggestion only...

    HTH.

    Rohit

  6. #6
    Join Date
    Nov 2002
    Posts
    13
    Unfortunately, the tables I'm using in the query are linked via ODBC (to Sybase), so I don't think zipping the database to attach will help. I took the two select statements I was using, saved them as individual queries and then created the union query using those, so it's a little easier to look at (below). I've tried creating a select query on this union query using Select Distinct and grouping, and I still get more records. What's disturbing is that the extra records are correct as far as what I'm trying to pull from the tables, so I don't know why they're not pulled by the individual selects. Very odd...

    SELECT [TCS Export Asset AP ONLY Move].Type, [TCS Export Asset AP ONLY Move].[Bar Code], [TCS Export Asset AP ONLY Move].[Serial Number], [TCS Export Asset AP ONLY Move].[From Desk], [TCS Export Asset AP ONLY Move].[To], [TCS Export Asset AP ONLY Move].[Transfer Date]
    FROM [TCS Export Asset AP ONLY Move]

    UNION SELECT [TCS Export PC_Equip TCS ONLY Move].Type, [TCS Export PC_Equip TCS ONLY Move].[Bar Code], [TCS Export PC_Equip TCS ONLY Move].[Serial Number], [TCS Export PC_Equip TCS ONLY Move].[From Desk], [TCS Export PC_Equip TCS ONLY Move].[To], [TCS Export PC_Equip TCS ONLY Move].[Transfer Date]
    FROM [TCS Export PC_Equip TCS ONLY Move]

Posting Permissions

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