Query issue

**BRZ-Ryan** · 12-20-2013, 11:26 AM

Hello

I have a db where we enter scheduled events done at other companies. I have one table containing all of the companies, and another table where the events are recorded. In tblEvents, I record the companyID of the company from tblCompanies, and the remaining details for the event (date, time, contact info, misc details). I am putting together an 'event viewer' where I have comboboxes of all data for companies\events (populated via SELECT DISTINCT, so each item only shows once). When an item is selected in a combo, I want it to filter the records displayed in a sub-form table (of events) that match the criteria in the comboboxes. For example. if I select State=FL and ZIP=34343 and Date=12/20/13, it will show only records from the events table with that criteria. I have some comboboxes for tblCompanies (co name, city, state,zip) and some for tblEvents (event date, event time, etc).

The issue is, the criteria comboboxes are populated with data from two different tables and I'm not sure how to handle this with SQL. I previously had it running when all data was stored in just tblEvents, but I recently moved all companies to their own table so I'm not sure how to re-write the query.

My thoughts:
1. Company name is selected (combo is 2 column, 1=ID,2=Name, bound to col 1)
2. Query Company table to return only records with matching company name

3. Query events table to return records with matching company ID's returned from previous query

Thanks in advance for your time!

Table info for which I have combobox criteria for, table names followed by field names:

Code:

Companies (CompanyID is primary key, no criteria is used for this, though)
-CompanyName
-City
-State
-ZIP
-Membership
-HasCommercial (yes\no checkbox)

Events (ID is primary key, and there is a criteria combo to select by ID)
-ID
-Date
-EventType
-Scheduler

Here's my query from when all data was in Events table.

Code:

SELECT Events.ID, Company.CompanyName, Events.MembershipNumber, Events.City, Events.State, Events.ZIP, Events.EventDate, Events.ScheduledBy, Events.EventType, Events.HasCommercial, Events.Attendees
FROM Events
WHERE ((Events.EventDate) = [Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbDate]) Is Null) 
And 
((Events.ScheduledBy) Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy] & "*" Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy]) Is Null) 
And 
((Events.EventType) Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbEventType] & "*" Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbEventType]) Is Null)
And
((Events.ID) Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbID] & "*" Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbID]) Is Null)
And
((Events.CompanyName) Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany] & "*" Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany]) Is Null) 
And 
((Events.MembershipNumber) Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbMembership] & "*" Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbMembership]) Is Null) 
And 
((Events.City) Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCity] & "*" Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbCity]) Is Null) 
And 
((Events.State) Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbState] & "*" Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbState]) Is Null) 
And 
((Events.ZIP) Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbZIP] & "*" Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbZIP]) Is Null) 
And 
((Events.HasCommercial) = [Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial] Or ([Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial]) Is Null)
ORDER BY Events.CompanyName;

**BRZ-Ryan** · 12-20-2013, 12:02 PM

Also, I have a sub-form on this form which I have bound to the companies table and I am successfully filtering this form based on criteria in the company comboboxes. Is it ok to use the Filter property on combo change\click? or should this all be in a query? Now I need to get the subform of events to only show events if the event-companyID is one of the companies in the companies subform, and the remaining event criteria matches.

**Dal Jeanis** · 12-20-2013, 05:31 PM

I would note that if

Code:

([Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy])

is Null, then

Code:

(Events.ScheduledBy Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy] & "*")

will be true for every record.
Any value is like "**" , so you don't need to test the Null condition for that text box.

This is true for every condition except the date and the checkbox.

I would also note that the query couldn't have worked like that, because the Company table is not included in the FROM clause, and is included in the SELECT clause. I've changed it to Events.

Code:

SELECT 
   Events.ID, 
   Events.CompanyName, 
   Events.MembershipNumber, 
   Events.City, 
   Events.State, 
   Events.ZIP, 
   Events.EventDate, 
   Events.ScheduledBy, 
   Events.EventType, 
   Events.HasCommercial, 
   Events.Attendees
FROM 
   Events
WHERE 
      ((Events.EventDate = [Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] )
    Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] Is Null) )
   And (Events.ScheduledBy Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy] & "*") 
   And (Events.EventType Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbEventType] & "*")
   And (Events.ID Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbID] & "*")
   And (Events.CompanyName Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany] & "*") 
   And (Events.MembershipNumber Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbMembership] & "*" ) 
   And (Events.City Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCity] & "*" ) 
   And (Events.State Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbState] & "*") 
   And (Events.ZIP Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbZIP] & "*") 
   And ((Events.HasCommercial = [Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial]) 
    Or ([Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial] Is Null))
ORDER BY 
   Events.CompanyName;

**Dal Jeanis** · 12-20-2013, 05:36 PM

Assuming that you put a key on the company record, and the company's Key into the events record, then your query will look something like this:

Code:

SELECT 
   TE.ID, 
   TC.CompanyName, 
   TE.MembershipNumber, 
   TE.City, 
   TE.State, 
   TE.ZIP, 
   TE.EventDate, 
   TE.ScheduledBy, 
   TE.EventType, 
   TE.HasCommercial, 
   TE.Attendees
FROM 
   Events AS TE
   INNER JOIN 
   Companies AS TC
   ON TE.CompanyID = TC.CompanyID
WHERE 
      ((TE.EventDate = [Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] )
    Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] Is Null) )
   And (TE.ScheduledBy Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy] & "*") 
   And (TE.EventType Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbEventType] & "*")
   And (TE.ID Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbID] & "*")
   And (TC.CompanyName Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany] & "*") 
   And (TE.MembershipNumber Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbMembership] & "*" ) 
   And (TE.City Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCity] & "*" ) 
   And (TE.State Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbState] & "*") 
   And (TE.ZIP Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbZIP] & "*") 
   And ((TE.HasCommercial = [Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial]) 
    Or ([Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial] Is Null))
ORDER BY 
   TC.CompanyName;

By the way, I'm not sure whether your code would work as is, but generally I would code each of the concatenations like this, to pass the literal to the SQl inside of quotes:

Code:

 And (TC.CompanyName Like "'*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany] & "*'")

**BRZ-Ryan** · 12-20-2013, 06:00 PM

Thank you so much for your effort. I havent tested that yet, but by glancing at it I think some of your references are off. If you look at my table names\fields in my post. Would I just change those to TC or is it correct as-is? Sorry, still learning on my own.

Code:

   TE.ID, 
   TC.CompanyName, 
   TE.MembershipNumber, <- this is in companies table
   TE.City, <- this is in companies table
   TE.State, <- this is in companies table
   TE.ZIP, <- this is in companies table
   TE.EventDate, 
   TE.ScheduledBy, 
   TE.EventType, 
   TE.HasCommercial, <- this is in companies table
   TE.Attendees

**Dal Jeanis** · 12-20-2013, 06:24 PM

Yes, just change them to represent the table they are in, TC for company table. Also fix the name of the CompanyID field, which I just made up as an example.

**BRZ-Ryan** · 12-22-2013, 01:26 PM

Thanks Dal, everything works except for the cmbCompany combobox. This combo holds the name of all the companies. I confirmed the query is being requeried on the comboboxes click\change event, as all of the other working criteria comboboxes. Any ideas?

Edit: Disregard, this is due to the 1st column being the company ID (not name), I added .Text to the combo criteria and now it works. Thanks!

**BRZ-Ryan** · 01-06-2014, 05:53 PM

I need a slight tweak to this and I cant figure it out. I tried to add another INNER JOIN but it's not working.

The TE.ScheduledBy value is the primary-key for a person in a people table. The query you provided is giving me the index and I cannot get it to retrieve the person in tblPeople based on the returned ID (TE.ScheduledBy). Any ideas?

**Dal Jeanis** · 01-07-2014, 09:05 AM

Please post (A) the EXACT SQL you had that was working (B) the EXACT SQL you tried that isn't working (C) a clear description of what the new SQL does in error.

FYI, it's better practice to start a new thread rather than resurrecting an old closed thread. I usually unsubscribe to threads a few weeks after they close. Sometimes just the act of writing up your complete description of the problem enables you to solve it on your own, and you learn more that way. A new thread also tends to get more fresh eyes on your new problem.

That being said, you can go ahead and post the description here and we'll see how we can help.

**BRZ-Ryan** · 01-13-2014, 10:46 PM

Here's what is used now and working (it returns the ScheduledBy persons ID (PK from Security table for the person) and I need name instead (LastName, FirstName):

Code:

SELECT TE.ID, TC.CompanyName, TC.MembershipNumber, TC.City, TC.State, TC.ZIP, TE.EventDate, TE.ScheduledBy, TE.EventType, TC.HasCommercial
FROM Events AS TE INNER JOIN Companies AS TC ON TE.CompanyID = TC.CompanyID
WHERE (((TE.EventDate Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] & "*")
    Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] Is Null))

   And ((TE.ScheduledBy Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy] Is Null))

   And ((TE.EventType Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbEventType] & "*")
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbEventType] Is Null))

   And ((TE.ID Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbID] & "*")
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbID] Is Null))

   And ((TC.CompanyName Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany] Is Null))

   And ((TC.MembershipNumber Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbMembership] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbMembership] Is Null))

   And ((TC.City Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCity] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbCity] Is Null))

   And ((TC.State Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbState] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbState] Is Null))

   And ((TC.ZIP Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbZIP] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbZIP] Is Null))

   And ((TC.HasCommercial = [Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial]) 
    Or ([Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial] Is Null)))
ORDER BY TC.CompanyName, TE.EventDate;

Here's what I tried to put together for this but it's not working

Code:

SELECT TE.ID, TC.CompanyName, TC.MembershipNumber, TC.City, TC.State, TC.ZIP, TE.EventDate, TE.ScheduledBy, TE.EventType, TC.HasCommercial, SC.LastName

FROM Events AS TE 

INNER JOIN Companies AS TC ON TE.CompanyID = TC.CompanyID

INNER JOIN Security AS SC ON TE.ScheduledBy = SC.ID

WHERE (((TE.EventDate Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] & "*")
    Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbDate] Is Null))

   And ((TE.ScheduledBy Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbScheduledBy] Is Null))

   And ((TE.EventType Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbEventType] & "*")
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbEventType] Is Null))

   And ((TE.ID Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbID] & "*")
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbID] Is Null))

   And ((TC.CompanyName Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbCompany] Is Null))

   And ((TC.MembershipNumber Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbMembership] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbMembership] Is Null))

   And ((TC.City Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbCity] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbCity] Is Null))

   And ((TC.State Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbState] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbState] Is Null))

   And ((TC.ZIP Like "*" & [Forms]![Navigation]![NavigationSubform].[Form]![cmbZIP] & "*") 
   Or ([Forms]![Navigation]![NavigationSubform].[Form]![cmbZIP] Is Null))

   And ((TC.HasCommercial = [Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial]) 
    Or ([Forms]![Navigation]![NavigationSubform].[Form]![chkHasCommercial] Is Null)))

ORDER BY TC.CompanyName, TE.EventDate;

**Dal Jeanis** · 01-14-2014, 08:53 AM

I don't see anything terrible in your syntax.

Verify that your Security Table has fields ID and LastName, and verify that ID on security table is the same field format as ScheduledBy on the Events table.

After that, try putting parenthesis in your FROM clause as follows -

Code:

FROM 
   (Events AS TE 
   INNER JOIN Companies AS TC 
   ON TE.CompanyID = TC.CompanyID)
   INNER JOIN Security AS SC 
   ON TE.ScheduledBy = SC.ID

I don't expect parenthesis to be the issue, but sometimes Jet gets finnicky, so I make a practice of being explicit in my order of joins.

**BRZ-Ryan** · 01-14-2014, 03:14 PM

That worked and I'm surprised it was just because I was missing parenthesis.

I also joined a third table and had to enclose like this (grouping\leveling my parenthesis)
FROM ((Events AS TE INNER JOIN Companies AS TC ON TE.CompanyID = TC.CompanyID) INNER JOIN Security AS SC ON TE.ScheduledBy = SC.ID) INNER JOIN EventTypes AS ET ON TE.EventType = ET.ID

Thanks for your help

**Dal Jeanis** · 01-14-2014, 03:23 PM

You're Welcome. That's why I label things as "my practice is to xxxxx" - because I never know if I'm REALLY keeping away the polar bears with my coding practices, or if I just THINK I'm keeping away the polar bears with my coding practices...

Query issue

Thread Tools

Query issue

Similar Threads

Relationships/query issue - Help!

Query Issue

Query Issue in MS-Access

Form / Query issue

Query Issue

Posting Permissions