Page 3 of 5 FirstFirst 12345 LastLast
Results 31 to 45 of 67
  1. #31
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I mean build another query that joins table and query by linking on the related primary/foreign key fields.

    Yes, that looks like appropriate table.



    However, advise no spaces or special characters/punctuation (underscore is exception) in names. Better would be EventNum or Event_Num and AttendeeID.
    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.

  2. #32
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I dont understand, sorry I'm still learning.

    How do I go about creating an attendee adding mechanism on the form...ie, combos that add a new combo once one is selected.

  3. #33
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't understand the question.
    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.

  4. #34
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I've seen something where there is a combobox with a list of items. When one is selected, another combo is created below it, to allow multiple selections from the list. I'm trying to figure out my attendee selection.

  5. #35
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That just sounds like data entry into a form set as Continuous or Datasheet view. Each selection in combobox is saved to a separate record. So what you see is the same combobox, but on separate records.

    How much Access experience do you actually have? What I describe is very basic Access functionality.
    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. #36
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    About 1-2 months worth, but I have 15 or so years of experience in programming. I think this is why I find myself attempting to do things in VBA because I'm not well versed in Access yet. I am interested in learning, though.

  7. #37
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    This is working great for count and monday-count, I just need to get it to show 0's for people with no events

    I'm learning as I go.. Do you write the SQL manually or use the query Design in Access?
    Code:
    SELECT Name, Count(*) AS Events, Sum(IIf(Weekday([Events].[EventDate])=2,1,0)) AS CountMon
    FROM (SELECT Attendees, Name, Access, EventDate FROM Events, Security WHERE (InStr([Attendees],[Name])>0) AND (Access="Attendee") AND (EventDate >= Date()))  AS Q1
    GROUP BY Name
    ORDER BY Count(*) DESC , Name;

  8. #38
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This is getting to be a long thread and can't remember what I've already suggested.

    Have I already mentioned that you need a 'master' dataset of all people and that you should do another query that joins the Count query to the AllPeople dataset?
    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.

  9. #39
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187

    Re: Query based on result of another query

    Isn't the master dataset my people table? How do I join one query with another? :-B

    I appreciate your patience

  10. #40
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That question does sound familiar.

    Yes, people table sounds like the 'master' dataset.

    Join queries the same way join tables. Open the query builder and pick tables and/or queries into the design window.
    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.

  11. #41
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I added people table to your query in design view, now my first persons monday count went from 1 to 34, and no 0 people are shown :|

  12. #42
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I did not say to add people table to the Count query. I said create another query that uses the Count query and the table.

    From the ribbon click Create Query. From the table/query list drag in the Count query and the table into the build window. Join the two on the Name fields. Set the join type to 'Include all records from People and only those from Count that are equal'.
    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.

  13. #43
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    It is now displaying the handful of people who have attended events, with blanks for people who do not have events. To the right, is all data from the People table. If a person\record from the People table has events, that person is shown with counts.. It looks like this:

    Mike Smith 55 5 2 Mike Smith MSmith@abc.com
    (empty space .....)Joe Smith JSmith@abc.com
    (empty space......)Tom Wilson TWilson@abc.com
    Paul Roberts 32 3 Paul Roberts PRoberts@abc.com

    This is the query based on what you instructed
    Code:
    SELECT *
    FROM AttendeeReportQueryAll RIGHT JOIN People ON AttendeeReportQueryAll.Name = People.Name;

  14. #44
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So now you have what you want? If you want the blanks to show as 0, try field calc in query or in textbox on report: Nz([fieldname],0)
    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.

  15. #45
    BRZ-Ryan is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    187
    I can fix the 0 part with Nz(), but I can't pull the name over from the right side. I tried People.Name instead of AttendeeQueryReportAll.Name but it tells me The specified field 'People.Name' could refer to more than one table listed in the FROM clause of the sql statement. I'm so close!

Page 3 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-27-2013, 05:24 PM
  2. Replies: 9
    Last Post: 05-08-2013, 02:37 PM
  3. Replies: 1
    Last Post: 06-08-2012, 11:14 AM
  4. combo box query based on different query result?
    By mejia.j88 in forum Queries
    Replies: 10
    Last Post: 02-15-2012, 02:00 PM
  5. Query result based upon two field conditions
    By diane802 in forum Access
    Replies: 35
    Last Post: 01-08-2010, 06:31 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