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

[jzwp11]

Are you putting the database in a trusted folder location as defined in Access? If not, code may not function.

[EDNYLaw]

Ha! If you don't hear it enough, let me say it, you're a freakin' GENIUS! You are one patient person, thank you so much for all your help. I'm actually beginning to see how to use Access correctly. I think I'm good for a little while, though I feel like I say that now and in 10 minutes some other problem will pop up.

[jzwp11]

You're welcome...

[EDNYLaw]

Ok here's my (your) database so far. I have one issue, then god willing this thing will be set.

When using the combo box for the workshop on frmAttendees it's not showing all the workshops that are in the Workshop Table. All the workshops are entered into the workshop table, and the control source is the [Workshops]![Workshop] and I really can't understand why it's not showing all workshops. The sessions combo box shows new sessions I added and the employee combo box shows new employees when added. Any thoughts would be appreciated! Thanks for all the help, yet again

[jzwp11]

That was my fault.

The row source of the workshop combo box is as follows:

SELECT Workshops.pkWorkshopID, Workshops.Workshop
FROM Workshops INNER JOIN (tblSessions INNER JOIN qryCountBySession ON tblSessions.pkSessionID = qryCountBySession.fkSessionID) ON Workshops.pkWorkshopID = tblSessions.fkWorkshopID
WHERE (((tblSessions.dteSession)>=Date()))
ORDER BY Workshops.Workshop;

This also impacts the workshop listing on the customer form.

In order to get the count of attendees in a session I used an INNER JOIN between tblSessions and the query that does the counting of attendees by session (qryCountBySession). Unfortunately, if a session does not yet have any attendees it is not included in the query and since that is joined via an INNER JOIN back to the workshops table, the workshop record is not returned.

In order to correct this I had to rework the queries in order to alter the join type from an INNER JOIN to a LEFT JOIN.

The corrected database is attached

[EDNYLaw]

Thank you so much, I'd have NEVER figured that one out. Your coding is WAY above my skill level. Thanks a lot for the help I really appreciate it.

[jzwp11]

Your skills will improve with time, practice & reading posts on forums like this. That's how I learned.

[EDNYLaw]

I plan to keep learning. I appreciate you taking the time to not only help me, but explain what you were doing. I get a lot of the concepts, but I was/am totally unfamiliar with the syntax of VB and SQL, I'm learning to program with java and XMLs for Android OS so I'm somewhat familiar with programming. But I'll definitely but cruising the forums for more info.

[jzwp11]

You're welcome & best of luck learning Java & XML. I would like to learn more about SQL Server & some webbased front end applications, but I just haven't made the time to do it.

[EDNYLaw]

Time is the biggest thing, I have to develop on Ubuntu and while it's somewhat user friendly, it's still a giant pain compared to Windows, or at least that's the way it seems since I've been using Windows since 3.1.

You wouldn't know why the list box query and enrolled status isn't updating, would you? It may just be a setting on my computer here (I've turned on trusted sources and all the VB is working) but the enrolled count isn't updating. The reason I think that it may be my work computer is, I downloaded the DB(Rev 4) on my home computer and the fields updated correctly I tried both Rev4 and Rev5 at work and neither update the list box query. I can't think of a setting that would do this. So many security settings... ugh

[EDNYLaw]

Nevermind, I figured out why one wasn't updating, because it's only showing the Spanish Foreclosure Workshop and not the English and I was adding to the English. I'm going to have to figure out why it's not Showing the English Session. Maybe something to do with the language field, I'll have to play around

Edit: Yes, it definitely has to do with the language field. I changed a random workshop to Spanish and it no longer updated. Hmmm... identified the problem, now to figure out the solution

[jzwp11]

The list box will not update until you leave the attendee subform. It seemed to work OK in the database I posted last night.

[EDNYLaw]

Yeah it seems to be only language related, as of now I'm just going to take out Spanish just to start entering the data in and making the transition. I'm not sure why the language becomes a factor but I'm going to try and diagnose it once I enter a TON of data