Creating a query/report that displays data from two tables and totals one set of data

[EDNYLaw]

So here's my question, which a significant amount of Googling has not resolved.



I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.

What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].

So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].

Here is a link to an Example Database http://db.tt/kJyLm4Nr, if anyone could give me a hand with this it'd be much appreciated.

I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.

And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.

[jzwp11]

I want to avoid creating separate tables for each workshop.

A separate table for each workshop would violate normalization rules and would cause you more headaches.

Also I am not sure what a text box query is, but from looking at your form, it appears that you are referring to the list box on the form. If you just want to add a count for the number of people enrolled in each workshop, you would handle that with a query and join that query back to the workshop table in another query. You would just base the list box on that new query. That is fairly easy to do; now for the bad news.

From looking at your tables, you are repeating the workshop name (date & time) in the attendees table. One of the rules of relational databases is that information should only exist in 1 table and you would link that information as necessary in another table. Typically that linking is done with key fields which is why Access prompts you to add a primary key field when you create a table. That field typically has a default name of ID, so you should be linking via the ID field not the text field (workshop name). The linking field in the related table is generally called the foreign key field.

Additionally, you will probably end up repeating the customer name & number several times in the attendees table if the same customer attends multiple workshops. It would be best to put the customers into their own table and then link them to the attendees table (again via the primary key--foreign key field relationship)

Just some general recommendations:

1. It is best not to use spaces or special characters in your table or field names. (I removed all of the spaces you had)
2. It is best not to use reserved word as table or field names. In your database, you use the word "time" as a field name. Time is a reserved word, so I change that. Here is a site that has a complete list of reserved words and symbols.
3. Do not use lookups (combo/list boxes) at the table level. This site explains why they are not a good idea. You did not have any table level lookups in your tables, so that was good.

In the attached database, I modified your table structure (tables and the relationships between them), field names etc. based on my discussion above. I removed everything else (forms, queries, reports etc.) since changing the table structure renders them useless. You will want to look at the relationship window to see how I have joined the tables.

Before proceeding with forms, queries and reports, it is critical that the table structure be set up properly first. To that end, I see one more problem that I did not correct in the attached database. It has to do with your workshop table. You have the same workshop repeated i.e. SCDOL Orientation but with 2 different dates as 2 records in the workshop table. If a workshop can have many session dates that describes a one-to-many relationship, so the sessions should be in their own table (linked to the workshop table of course), and it is the particular session that has the attendees. Similarly, you have the Foreclosure Prevention Counseling workshop--but in two different languages. So that implies that the language is related to the session of the workshop being conducted not the workshop directly. Adding a table for sessions is a bigger changed compared to what you had initially, so I wanted to explain that in more detail rather than just making that change in your database.

[EDNYLaw]

Thanks for the help! I taught myself Access because I wanted to implement something for this issue. Prior to this they were recording this stuff on paper and it was archaic and their solution was to just start entering it all on an Excel sheet. But I'm completely open to learning the correct way to do things. I'm not opposed at all from starting from the ground up. I really appreciate your help.

Yeah, each workshop can have multiple dates depending on what ends up being scheduled for the month, nothing is static into the future. I wasn't sure how to create something that allowed the entries of the workshop and the dates and easily allowed me to tally those workshops, since each workshop on a specific date has a limit for the number of attendees.

[EDNYLaw]

Being that I'm really unfamiliar with the whole Primary Key/Foreign Key thing, can you explain a little about what you did in the relationships? Where I'm confused right now is, I see how the Attendees table is recording the IDs of Customer, Workshop and Employee but if I want a form that has a combo box for the workshops (and one for the employees but I'd imagine the process will be largely the same) what is the control source of that combo box so it records the workshopID to that customer and what table do I base the form off of (I'm guess tblCustomers)?

[jzwp11]

It sounds like you really need the session table. I've attached the database with the session table added and made the changes to the other tables & the joins.

Getting the counts can be handled with an aggregate query that groups by workshop and then session.

The primary key--foreign key is how two related tables are joined.

Just as you showed the workshop name in the attendee table, you were essentially using that field as a foreign key to the workshop name field of the workshop table.

With a join, you only need that value to join the two tables, so repeating other fields is unnecessary (and a violation of normalization rules)

What I have done in the attached database is to just replace those duplicating field with only 1 field.

For example if a session has a primary key value of 2, you should see a 2 in the attendee table for all people attending that particular session.

[EDNYLaw]

Alright, that's pretty awesome. I hope I'm not pushing too much, as I'm very thankful for the help you've given thus far, I'd have never gotten this far on my own. But, which table would I base a form off of? And by form I mean a form similar to the form I had in my first post, where users can just enter the customer info, select a workshop and employee from the drop downs, and I guess now select which date for the workshop. And for the combo boxes, what's the control source? I'm not really sure how I get the combo boxes to record the workshop if the records are kept using the ids but the combo boxes display the text.

Sorry for the total noobishness but this stuff is complicated when you've never been taught it before

[jzwp11]

But, which table would I base a form off of?

Typically you would have a main form/subform combination. You would base the main form on the customer table and the subform would be based on the attendee table. Please take a look at frmCustomer in the attached DB.

, select a workshop and employee from the drop downs, and I guess now select which date for the workshop

To set up both the workshop and the session you need a technique called cascading combo boxes. You would select the workshop and the combo box for the sessions would only show the sessions available for that workshop. In order to have Access show the workshop on existing records, you have to make a compromise on normalization by including a field for the workshop in the attendee table in addition to the session field. I have set that up in the subform for you. I typically like to set the row source of the second (session) combo box when a selection is made in the first, so I did use some Visual Basic for Application (VBA) code in the after update event of the workshop combo box to accomplish that.

And for the combo boxes, what's the control source? I'm not really sure how I get the combo boxes to record the workshop if the records are kept using the ids but the combo boxes display the text.

The control source would be the key field (generally the foreign key field). You can hide this field from the user by setting its column widths property to zero. I have done that in the attached as well.

Now you asked earlier about showing the count of people in a particular session which as I mentioned can be obtained via a query. So I show a list box on frmCustomers with that information. You can also prevent a user from adding a session to a customer if the session is already full; that does take some VBA code as well. I have included that in the After Update event of the session combo box in the subform.

[EDNYLaw]

Oh my god, you are truly awesome! That's just amazing, I'm going to dissect all of this and come back with what I'm sure will be myriad questions, but seriously, thank you so much for your help. That was just a great explanation and the fact that you took the time to create the db so I could see how it works, awesome!

[jzwp11]

You're welcome. It's been a slow day here at work, so I had the time.

[EDNYLaw]

So I've got yet another question. In frmAttendees, is there a way for the Sessions combo box only to display the values associated with the workshop selected in the workshop combo box? Basically I want to avoid employees signing up customers for the workshops that don't exist, I work for the state and the hiring standards, well... they're low to say the least

[EDNYLaw]

And yet another question: How can I design a query that allows an employee to select a specific workshop (and date) and display the customers attending (I need NY Number, First Name, Last Name, Phone Number and a blank signature column [sig column not 100% necessary])?

[jzwp11]

In frmAttendees, is there a way for the Sessions combo box only to display the values associated with the workshop selected in the workshop combo box?

It should already be doing that.

I want to avoid employees signing up customers for the workshops that don't exist,

I also put in code that checks to see if a particular session is full. After the session is selected, a message will be returned if that session is full & forces the person to reselect another session.

There is no current way for the user to select workshops that do not exist, so you might have to explain what you mean by that.

Now, you could limit the workshop combo box to only show available workshops (where at least 1 session is open), but the problem comes in if you use the same form for adding new attendees to workshops as well as editing existing attendee/workshops because if you limit the combo box to available workshops only, the combo box will not show the name of the workshop for those workshops that have already occurred or where all session are full. I would almost be tempted to use a separate form for adding new attendee records versus editing existing records. (You would typically have a navigation form for users to make a selection based on what they want to do & based on their choice open the correct form for them).

And yet another question: How can I design a query that allows an employee to select a specific workshop (and date) and display the customers attending (I need NY Number, First Name, Last Name, Phone Number and a blank signature column [sig column not 100% necessary])?

To do this, you would use an unbound form with two combo boxes (one for the workshop and the other for the sessions). You would filter the second combo box based on the workshop chosen in the first combo box. You would use code in the After Update event of the first combo box to filter the row source of the second. I actually did this already in the frmAttendee. The code would look like this

Dim mySQL As String

mySQL = "SELECT qrySessionInfo.pkSessionID, qrySessionInfo.fkWorkshopID, qrySessionInfo.dteSession, qrySessionInfo.txtLanguage, qrySessionInfo.SessionTime, qrySessionInfo.AttendeeLimit"
mySQL = mySQL & " FROM qrySessionInfo"
mySQL = mySQL & " WHERE qrySessionInfo.fkWorkshopID=" & Me.cboWorkshop
mySQL = mySQL & " ORDER BY qrySessionInfo.[dteSession]"

Me.cboSessions.RowSource = mySQL


Your session combo box would have to be named cboSessions for the above code to work.

On that form you would also have a button that opens a form based on a query that has the information you want. The button will use the selected workshop and session to filter the form as it opens. See frmGetAttendeeList in the attached.

[EDNYLaw]

Wow... awesome! I'm going to try all that out, since my job is rather slow today. As usual, thanks for the help!

[jzwp11]

You're welcome.

[EDNYLaw]

So I'm following your comments, but I've noticed a few things:

1. On frmGetAttendeeList the second combo box is not being filtered by the first combo box selection
2. On frmGetAttendeeList the Open Form button is not working when clicked
3. On frmAttendees the Session Combo box is not being filtered by the Workshop combo box (ie I can add someone to a workshop that doesn't exist, like cover letter writing on 8/3 when the only class is 8/20)

I'm starting to think that VB doesn't work on my system. I had previously tried to use some VB stuff and could not get it to work at all, even simple things like capturing an error code. Is there some default setting that I'm missing that allows VB to operate?