Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Red face Unanswered: Query Results on a form

    Hello all,
    i have this issue database that i have built and the main purpose of this database is to keep track of issues as user types them in.
    What i would like to do is to display on the main form with names and amount of issues people have entered, to do that i have done a query which spits gives me what i wanted.
    here is the query
    SELECT Issues.[Opened By], Count(Issues.[Opened By]) AS [CountOfOpened By]
    FROM Issues
    GROUP BY Issues.[Opened By];

    Using that query I have tried subform OR list box to display the query result on a form but for some reason it shows numbers instead of name but it does gives me the quantity for example.
    name count of Opened By
    1 5
    2 9

    what i would like is
    name count of Opened By
    john 5
    james 6

    on a main form. What I have also noticed is that opened By is a combobox field with first and last names in it.

    Any help would be nice

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    are you by any chance using the evil lookup "feature" from A's table design?
    yours is a classic example of the confusion that results. the lookup "feature" tricks you into thinking that the name is stored in the table - it isn't!

    if you have used the dreaded lookup...

    ...try query design, add the Issues table and whichever table holds the name of the person... they should auto-join for you but if not drag the userID from one to the other so they join. add your count & your group. view that query in SQL view. your code needs to look something like that.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Lightbulb

    Greets;

    I have had this issue when using the list/combo boxes.

    It often display the Autonumber/ID of the person rather than thier name. It is useful to have the ID number in thereif you wish to open another form associated with that list box. Ie with your example, open the form associated with the issues.

    You can quite easily create a list box using the Wizard when building the forms in Access. Be sure to select ALL the fields you wish to use from your query. EG: ID, First name, last name, count_of_issues.

    You do need all these fields in the query that it is working from, and what i tend to do if the names are from seperate tables from the issues, i would add that table into the query, and select the names from the names table:

    eg; user id (as part of the relationship from the issues table)
    first name (from the table of names)
    last name (from the table of names)
    count of issues (from issues)

    When you create your list box, under the properties settings you can adjust the field width so that it displays better, and also select the linkable field (eg user id). If you have a linkable field you can have a button on the bottom that opens another form associated (eg to view all John's issues)

    I hope this helps somewhat, if not feel free to reply and i'll try and help some more.

    DAn

  4. #4
    Join Date
    Jul 2005
    Posts
    144
    izy,
    thanks for the quick reply, My main table is called Issues and it has a field called Opened By which is connected to a tbl called contacts which stores first and last name.
    Query seem to work fine when i run it and I am sure you have seen the sql view of the query above.
    Here is What I do to add my query to the form, I open the form in design view and then i choose list box and it opens up the wizard and i simply follow it to the finish and same goes for subform.
    But instead of a name on the Opened By field i get numbers. However query displays results just fine which doesnt make sense to me that why cant i see the same thing on the form. I went ahead and tried your way but the query results are off a bit, they are showing me less records next to the name then i suppose to have.
    here is what i did after what you told me.
    SELECT Issues.[Opened By], Count(Issues.ID) AS CountOfID
    FROM Contacts INNER JOIN Issues ON Contacts.ID = Issues.ID
    GROUP BY Issues.[Opened By];

    BTW there is not such field in my table thats called count of query. Its just there in the query only.

    I think my query is right its just for some reason it doesnt show correctly on the form.
    Hope that helps


    Thanks
    Last edited by ranjah; 04-28-06 at 15:38.

  5. #5
    Join Date
    Jul 2005
    Posts
    144

    Wink

    Quote Originally Posted by danlindley
    Greets;

    You do need all these fields in the query that it is working from, and what i tend to do if the names are from seperate tables from the issues, i would add that table into the query, and select the names from the names table:

    eg; user id (as part of the relationship from the issues table)
    first name (from the table of names)
    last name (from the table of names)
    count of issues (from issues)

    DAn
    WOW, very good explanation Dan, above in quotes is the part that is not working to begin with, however my query works fine but how can I display it correctly in the form is the problem i am having. Any thoughts on that??
    here is my query again

    SELECT Issues.[Opened By], Count(Issues.[Opened By]) AS [CountOfOpened By], Issues.Status
    FROM Issues
    GROUP BY Issues.[Opened By], Issues.Status
    HAVING (((Issues.Status)="active"));


    Thanks again
    Last edited by ranjah; 04-28-06 at 15:39.

  6. #6
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Quote Originally Posted by ranjah

    SELECT Issues.[Opened By], Count(Issues.[Opened By]) AS [CountOfOpened By], Issues.Status
    FROM Issues
    GROUP BY Issues.[Opened By], Issues.Status
    HAVING (((Issues.Status)="active"));
    It looks as though the field [Opened by] is the ID of the person that has opened. I don't know SQL very much so a stab in the dark to try and achieve something.

    You will need to SELECT [first name] and [last name] from the table that has your users in it...

    then...

    add the fields [first name] and [last name] to the section of the SQL statement that pulls out the data. It should still be linked apporpraitely. And youd get something that would display:

    3 | John | Doe | 23
    ID first Last count

    you could always switch form the SQL builder to the query builder display, add the table "users" and drag and drop the first name and last name fields to the query.

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  7. #7
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Example attached

    here is an example i created for you, it uses three tables:

    users:
    magazines:
    sales:


    there 2 queries that group & count the number of magazines sales per user, and two forms:

    what you have:
    what you need:


    The form What you have displays the information from the query sales per user this displays the user id (as is standard) with the count.

    The form What you need, displays the info from the query sales per user ammended, this one displays the first and last name with the count.

    I figured it may help you visualise this with an example... hope it helps.

    Dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  8. #8
    Join Date
    Jul 2005
    Posts
    144

    Talking Thanks

    Thanks alot DAN for making a helpful example for me, it worked like a charm!

    Detail,
    After looking at your example i knew what i need to do and this is what i have done to make it work.

    SELECT Contacts.[First Name], Contacts.[Last Name], Issues.Status, Count(Issues.[Opened By]) AS [CountOfOpened By]
    FROM Contacts INNER JOIN Issues ON Contacts.ID = Issues.[Opened By]
    GROUP BY Contacts.[First Name], Contacts.[Last Name], Issues.Status
    HAVING (((Issues.Status)="active"));

    just a couple of things
    1. If my originaly query works then how come i cant make it display on a form?? may be because of the combo box opened by??

    2. I choose yes to the colum heads and i see my field names which is fine but the last field is named count of id and if i want it to change it how can i do it?
    i mean if i try changing it with in the query it changes the query and nothing works.

    3. If I wanted to see one more field which is total numbers of issues with no status next to persons name how would I go about doing that??

  9. #9
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Wink

    Sorry it has taken a while to get back to you (i have been on nights at work), I'll see what i can do with regards to helping you out with your other problems...

    Thanks alot DAN for making a helpful example for me, it worked like a charm!

    Detail,
    After looking at your example i knew what i need to do and this is what i have done to make it work.

    SELECT Contacts.[First Name], Contacts.[Last Name], Issues.Status, Count(Issues.[Opened By]) AS [CountOfOpened By]
    FROM Contacts INNER JOIN Issues ON Contacts.ID = Issues.[Opened By]
    GROUP BY Contacts.[First Name], Contacts.[Last Name], Issues.Status
    HAVING (((Issues.Status)="active"));

    just a couple of things
    1. If my originaly query works then how come i cant make it display on a form?? may be because of the combo box opened by??


    All queries can become viasable, either as normal record entry or displayed in a combobox.

    Make sure you have the relationship link right from the form with your combobox, and the new form that you are opening. Also, make sure before opening the new form, select the record you wish to look at.

    It is fairly easy to do with the wizard... Create a new button on the form with your combobox > (the wizard will load) > select [i think] form operations > open form > then select the form you wish to open (eg frm_data) > then select "open the form and find specific information to display" > then select the ID column from that are related on both forms. (any probs with this and i can take a look at your db if you like.

    2. I choose yes to the colum heads and i see my field names which is fine but the last field is named count of id and if i want it to change it how can i do it?
    i mean if i try changing it with in the query it changes the query and nothing works.


    Type something like Newfieldname:[count_of_id], i'm not sure if that's right for SQL, but you can do it this way in the query view.

    3. If I wanted to see one more field which is total numbers of issues with no status next to persons name how would I go about doing that??

    Not sure what you mean, what ever you want to be able to display in your combo_box you need to have them in the backend in your query.

    - it is possible to merge info (providing they are related) from two or more queries and display all this information together. I may be tempted to create a seperate from with a combobox for this detailed query and add a button that says "view issues for no status" at the bottom.

    Hope this helps...

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  10. #10
    Join Date
    Jul 2005
    Posts
    144

    Cool

    [QUOTE=danlindley]Sorry it has taken a while to get back to you (i have been on nights at work), I'll see what i can do with regards to helping you out with your other problems...

    I really do appriciate you helping in your busy hour, i hope they give you a big raise

    Let me start by saying this my query works only if i have the link relationship right to begin with...
    I had relationship done by attaching both PK together to where you had it from PK to field(Opened By) which is why query works fine but for some reason when i try using that in a list box on a form i dont see any results.
    after i opened your relationship thats the first thing i noticed.

    "All queries can become viasable, either as normal record entry or displayed in a combobox"
    what do you mean by this can you elaborate that a little for me ??


    My very last request is to see total records from each individuals.
    here is my query at the moment and it displays issues from every individuals but active issues only.

    SELECT Contacts.[First Name], Contacts.[Last Name], Issues.Status, Count(Issues.ID) AS CountOfID
    FROM Contacts INNER JOIN Issues ON Contacts.ID=Issues.[Opened By]
    GROUP BY Contacts.[First Name], Contacts.[Last Name], Issues.Status
    HAVING (((Issues.Status)="active"));

    Query results are as follows..

    firstname lastname Count of Issues Status
    john doe 45 Active


    I would like to see an additional field

    firstname lastname Count of Issues Status Total Issues By User
    john doe 45 Active 50

    How can I make that work??

  11. #11
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    "All queries can become viasable, either as normal record entry or displayed in a combobox"
    what do you mean by this can you elaborate that a little for me ??


    you can have the results from a query displayed on a form in two ways, you can have either a combobox to view the results of the query as a list/summary, or the query can be applied to the form that handles data input, so only the content of the query can be seen. This would be in the data entry style so that records can be amended.

    I digress, i tihnk the first list/combo box is the most suitable approach and have the data entry be linked to a selected field in the combobox. But i thought it would be worth a mention that your results can be displayed as a record to a page and the user select next to view the next record.

    Anyhow...

    To display the extra column, i would create a separate query to handle just this info (qry_totalissues_byuser) then in the query that the main information is gathered from (qry_issues) i'd add this query into it, and ensure that the "user" field is linked in the relationship to both queries, this should enable this additonal field to be active in the query, then all you need to do is add it to your field list....

    As mentioned before i don't know any SQL, but the code would need to:

    SELECT [total_issues] from qry_issues_byuser,

    then using a snip of your code,

    GROUP BY Contacts.[First Name], Contacts.[Last Name], Issues.Status, qry_issues_byuser.[total_issues]

    It is a guess, anyway, if it doesn't work, feel free to attach your db as a zip, and I'll be more than happy to take a look and explain better how to do it/do it for you.

    Feel free to pm/email me anytime, if you have any problems in the future/ any more problems with this db, i'd be more than happy to help out where i can.

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  12. #12
    Join Date
    Jul 2005
    Posts
    144

    Talking Thanks alot

    To display the extra column, i would create a separate query to handle just this info (qry_totalissues_byuser) then in the query that the main information is gathered from (qry_issues) i'd add this query into it, and ensure that the "user" field is linked in the relationship to both queries, this should enable this additonal field to be active in the query, then all you need to do is add it to your field list....

    As mentioned before i don't know any SQL, but the code would need to:

    SELECT [total_issues] from qry_issues_byuser,

    then using a snip of your code,

    GROUP BY Contacts.[First Name], Contacts.[Last Name], Issues.Status, qry_issues_byuser.[total_issues]

    It is a guess, anyway, if it doesn't work, feel free to attach your db as a zip, and I'll be more than happy to take a look and explain better how to do it/do it for you.

    Feel free to pm/email me anytime, if you have any problems in the future/ any more problems with this db, i'd be more than happy to help out where i can.

    Dan[/QUOTE]

    I went ahead and tried it your way and it works great all i had to do is add the the new query with the old one and it works great.
    I really really appriciate it.

    THanks alot

Posting Permissions

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