Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking Unanswered: Records displayed

    I have a query with the following sql:
    Code:
    SELECT QY_ALL.TB_STAFF_STAFF_ID, QY_ALL.LNAME, QY_ALL.FNAME, QY_ALL.JOB_TITLE, QY_ALL.BASE, QY_ALL.DEPT_WARD, QY_ALL.STATUS, QY_ALL.COURSES_DONE_ID, QY_ALL.COURSE, QY_ALL.START_DATE, QY_ALL.END_DATE
    FROM QY_ALL
    WHERE (((QY_ALL.DEPT_WARD)=[Enter Dept/Ward]) AND ((QY_ALL.STATUS)="CURRENT") AND ((QY_ALL.COURSES_DONE_ID)=[SELECT COURSE ID]) AND ((QY_ALL.START_DATE) Between [Enter start date] And [Enter end date]))
    ORDER BY QY_ALL.LNAME, QY_ALL.FNAME;
    When I run a report off it it returns correct data except it treats same surnames as one. E.g. if I have Jon, Jo and Fred Smith I get Jon listed but Jo and Fred's records attributed to him.
    Is the sql wrong or do I fix it in the report?
    Thomas J Marshall

  2. #2
    Join Date
    Feb 2004
    Posts
    90
    The query looks fine.

    Have you tried running the query independently of the report? Do the results look right?

    My guess is your report is set up to group by LNAME.

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Yep, "My guess is your report is set up to group by LNAME." is my guess. If your report is setup so that it display's correctly using the group, then in your query, add a field FullName:[LName]&[FName] and change the report to group on that field.

    Also, performance note: if y ou are using any kind of sorting/grouping in the report, don't bother having it in the query - Report Grouping trumps Report Sorting which trumps Query Sorting (in other words, the Query sorting is just extra work for your processor).

    tc

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I agree that the SQL looks okay. Does running the query alone return records as you desire? If not, since it looks like this query is based on another, it could be that the other query is where the problem is. If it does, then the problem is probably in the report as Goldy has mentioned.
    Paul

  5. #5
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking

    Thanks for that. Yes, I've grouped on lname in the report. How do I get a full name into a query? I have a control on the form that takes fname and lname and displays them but how do I do it in a table or query?

    Thanks.
    Thomas J Marshall

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    see tc's post:
    then in your query, add a field FullName:[LName]&[FName]
    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24
    Quote Originally Posted by izyrider
    see tc's post:
    then in your query, add a field FullName:[LName]&[FName]
    izy
    I've done that and ran an update query to slot in fname and lname but I'd like to populate the fullname whenever I type in lname and fname on my form.

    How can I do that?

    Thanks.
    Thomas J Marshall

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nonononononono!

    you are not going to add a field to your table for FullName.

    your query (your report does run off a query doesn't it? if not, fix it!) does the FullName stuff from the existing Lname & Fname

    a bit like pix below

    izy
    Last edited by izyrider; 02-05-08 at 14:24.
    currently using SS 2008R2

  9. #9
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking

    Quote Originally Posted by izyrider
    nonononononono!

    you are not going to add a field to your table for FullName.

    your query (your report does run off a query doesn't it? if not, fix it!) does the FullName stuff from the existing Lname & Fname

    a bit like pix below

    izy
    Yes, it runs off a query. I don't have fname/lname in the query as in your screenshot, just as fields. Looking at your screenshot do I just need to enter the code in the query?

    'fraid I've already added the fullname field and populated it!! I then added it to the query and I can sort within the report OK now. I thought if I could get fullname to take the data I enter into fname and sname that would be it.

    Why's it bad to add the new field?
    Thomas J Marshall

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Why's it bad to add the new field?
    At first glance, it wouldn't seem to be "bad" but it violates one of the basic principles of DB design (storing duplicate information) and one of the basic principles of software develpoment (creating extra work for the PC and more importantly, YOU).

    On the flip side, I have had instance where it made sense to do that, such as when a tool number is built off a key value (AutoIncrement) but displayed in some archaic format "8T"&Format(ToolKey,"00000"). Sure, I could format is in forms, reports and even queries, but it just gets to be a pain after a while. Since the value can not be changed by the operator, I saved some of the additional fiddling around by doing it up front.

    In this case, if you add to your query the snippit I posted earlier, it will "create" that field in your query and populate it with the correct information based on the table (this makes it DYNAMIC which is a good thing). The text on the left side of the colon is the name of your new field. The text on the right side of the colon is the expression that populates the new field. The & symbol concatenates the values of the two fields (meaning it puts them together, one after the other in one string).

    This is prefered for a variety of reasons, including: using the additional field in the table requires you police every possible way of changing one of the names to make sure the combined name gets updated. May not be a big deal now, but down the road, especially when somebody else takes over the project .....

    tc

  11. #11
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Talking

    Quote Originally Posted by tcace
    At first glance, it wouldn't seem to be "bad" but it violates one of the basic principles of DB design (storing duplicate information) and one of the basic principles of software develpoment (creating extra work for the PC and more importantly, YOU).
    I had a vague idea that might be so! Honest!

    In this case, if you add to your query the snippit I posted earlier, it will "create" that field in your query and populate it with the correct information based on the table (this makes it DYNAMIC which is a good thing). The text on the left side of the colon is the name of your new field. The text on the right side of the colon is the expression that populates the new field. The & symbol concatenates the values of the two fields (meaning it puts them together, one after the other in one string).
    I did and it works a treat. I put it in the main query that I base a lot of queries on so I just add it in as I like.

    Thanks for all your help.
    Thomas J Marshall

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You are welcome!

    Glad to help.

    tc

Posting Permissions

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