Results 1 to 9 of 9
  1. #1
    Two Gun is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    22

    Query Data Incorrect

    I have two tables called Table A and Table B.

    Table A has a autonumber as primary key and the table is updated manually. Table B is linked to an Excel file (where data is downloaded daily from our system) and has no autonumber primary key.

    There is one field in both tables called POS_NUM that are identical. I want to build a report based on Table A, with a subreport based on Table B. The parent/child link is on the POS_NUM field.

    I created a query with both tables and joined the tables by POS_NUM. When I run the query, it appears to capture the data I need, but with a glitch I can’t seem to fix.



    In Table A (not Table B) there are some position numbers that have more than one person assigned to it. In those cases, my data doesn’t capture the individual names of all those assigned to that particular number. For example, if my query pulls position number R0300 and 3 different people are assigned to it, three records are pulled, but the same name is identified for all three records. In my Table A data, this is the name of the first person assigned to it, but there are two other persons assigned as well.

    I need my query to pull records for each unique position number, and for those position numbers with multiple persons assigned, to identify each of those records with the unique names of all those assigned.

    Appreciate the help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I did a test with data as you describe and all individual names show regardles of join type. Provide sample data. Post your query SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Two Gun is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    22
    Most position numbers have only one person assigned; several have mulitple persons assigned. This is snapshot of what I get when more than one person is assigned. Note: A total of three (3) people are assigned to position number 0000120, but only John Smith shows up.

    ID
    Employee Name
    Title
    PP
    Series
    Grade
    Pos_Num
    67
    John Smith
    Clerk
    GS
    0303
    05
    0000120
    67
    John Smith
    Clerk
    GS
    0303
    05
    0000120
    67
    John Smith
    Clerk
    GS
    0303
    05
    0000120


    Here's the SQL statement:

    SELECT [Table A].ID, [Table A].[Employee Name], [Table A].Title, [Table A].PP, [Table A].Series, [Table A].Grade, [Table A].Pos_Num
    FROM [Table A] RIGHT JOIN TABLE B ON [Table A].Pos_Num = Table B.[Pos_Num]
    WHERE ((([Table A].Bureau) Like "*" & [Enter BUREAU] & "*") AND (([Table A].Organization) Like "*" & [Enter ORG CODE] & "*") AND (([Table A].Superseded)=No));

  4. #4
    Two Gun is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    22
    Sorry about the formatting - the data should look like this:

    Employee
    ID Name Title PP Series Grade Pos_Num

    67 John Smith Clerk GS 0303 05 0000120
    67 John Smith Clerk GS 0303 05 0000120
    67 John Smith Clerk GS 0303 05 0000120

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why are you joining to table B if you don't pull any fields from table B?

    I want to build a report based on Table A, with a subreport based on Table B.
    If you want to do a report/subreport, no reason to join these tables in one query anyway.

    I need my query to pull records for each unique position number, and for those position numbers with multiple persons assigned, to identify each of those records with the unique names of all those assigned.
    Isn't this all in Table A?

    Again, I can't replicate your issue by constructing data as best I can understand from your description. Need your data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Two Gun is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    22
    My mistake ... this information dropped off the end of my select statement. Probably makes a difference.

    Table B.[Pos_Num], Table B.[Position Code], Table B.[Position Level], Table B.[Position Routing]

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Nope, still can't replicate. My table A has 5 records with 4 POS_Num and 5 employee names. Table B has 4 records of the same POS_Nums. The query shows 5 records reflecting Table A POS_Num/EmployeeName.

    In your results, it's like the query is seeing the Table B names even though your query is pulling table A. Do you have table descriptons switched?

    Need your project if you want to provide it. Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post.

    Still, if you want a report/subreport, why this query?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Two Gun is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    22
    Honestly, as a novice, I'm very quickly getting over my head. You ask why this query? It seemed the best way (source) for creating a report, with a subreport linked by position numbers, that would allow me to compare selected data in Table A, with selected data in Table B.

    Is there a better way? Thanks for your patience, I really appreciate it, and yes, it's difficult to share the data, much of which is sensitive.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Should be able to use this query as the source for a report (not data entry) and would not need subreport. Set up Grouping & Sorting in the report. However, need to get the query working first. As I said, my dummy data works. Must be something about your data structure I am not replicating.

    I don't care about real data, it is the structure I need to analyse. Make copy, remove confidential data (leave some dummy records for testing or just remove data from specific fields and/or extract just the relevant tables to another project), run Compact & Repair, zip if large, attach to post.

    Can't do anything else without that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Incorrect Calculations in Report
    By Bridiewms in forum Reports
    Replies: 6
    Last Post: 01-30-2012, 10:28 AM
  2. Incorrect Sums
    By Azyrus in forum Reports
    Replies: 6
    Last Post: 06-26-2011, 04:27 PM
  3. Incorrect sorting in report.
    By jonesy29847 in forum Reports
    Replies: 2
    Last Post: 06-16-2010, 05:56 PM
  4. Subform loading incorrect data
    By GoVols02 in forum Forms
    Replies: 3
    Last Post: 02-12-2010, 09:53 AM
  5. Replies: 0
    Last Post: 08-01-2009, 12:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums