Queries For ClearancedB

[mackson]

Hi all!
I would like to build queries on a database that is uploaded here: ClearancesVF.zip


The database has been prefilled with some data. The goal of the queries is
o To highlight expired clearances with complete infos like name, surname, type of clearances date of expiry, etc :

• by company/Enterprise
• by entity
• per person

o Be able to highlight/sum up the clearances that will expire in a certain period of time ones can specify [either in term of days ( maybe much easier), or months or years]
o If possible, be able to make statistics for example, the percentage of authorization expired and/or not expired at a given moment (maybe current time)

But before that I would like to add a field for clearances card number of each person. This number is specific to each person who has one or several clearances. It should be entered manually in numerical or text format [XXXX/XXXX] i.e. - Order No./Year, for example No. 0800/2022. Probably a table should be redesigned to add the field for card number.

regards

[kd2017]

I would combine the vehicles, entities, and enterprises tables into one table. Add a field to this table that indicates which type each record is. This will eliminate at least 4 tables from your current design and simplify your query.

That said, here is an example query to get 'enterprises' with a DateTo that is less than the current date, presumably these are expired.

SELECT TblPeople.PeopleID, 
       TblPeople.FirstName, 
       TblPeople.Surname, 
       TblPeople.[Prof ID], 
       tblEnterprises.EnterpriseID, 
       tblEnterprises.Enterprise, 
       tblPeopleEnterprises.DateTo 
FROM   TblPeople 
       INNER JOIN (tblEnterprises 
                   INNER JOIN tblPeopleEnterprises 
                           ON tblEnterprises.EnterpriseID = 
                              tblPeopleEnterprises.EnterpriseID) 
               ON TblPeople.PeopleID = tblPeopleEnterprises.PeopleID 
WHERE  (( ( tblPeopleEnterprises.DateTo ) < Date() ));  

[mackson]

Hi
Following your logic I built this query to extract expired clearances per person:
to get this result:
When you write this

I would combine the vehicles, entities, and enterprises tables into one table. Add a field to this table that indicates which type each record is. This will eliminate at least 4 tables from your current design and simplify your query.

, but suppressing tables means redesigning the database ? No ?

[mackson]

I get what you mean: by combining the tables, Vehicles, entities, and enterprises that would make the query easy. But How should I combine them in one table and how will thi new table be filled ( ie relationship) to account for the data in vehicles entities and enterprises?
regards

[mike60smart]

Hi
You have created the query correctly which shows you all the expired Vehicles for specific People.

What do you think is wrong with this query?

[mike60smart]

After having a rethink of your requirements your relationships should be as follows:-

[mackson]

Hi!
If understand, the new relations of the db should look like this:

But I am having issues to set the referential integrity between tblTypes and tblPeopleAllocations:"Data in table 'tblPeopleAllocations' violates referential integrity rules".
I checked the types of data of typeID in both tables, they are similar

[mike60smart]

Hi

I have had a rethink and the database should be as follows:-

[mackson]

OK! The frmpeople is the entry form as usual. Unfortunately the queries requires table like
* tblPeopleEnterprises for the qryEnterprises

SELECT TblPeople.PeopleID, [FirstName] & " " & [Surname] AS Person, TblPeople.[Prof ID], tblEnterprises.Enterprise, tblPeopleEnterprises.DateFrom, tblNrYears.NrYears, tblPeopleEnterprises.DateTo
FROM tblNrYears INNER JOIN (TblPeople INNER JOIN (tblEnterprises INNER JOIN tblPeopleEnterprises ON tblEnterprises.EnterpriseID = tblPeopleEnterprises.EnterpriseID) ON TblPeople.PeopleID = tblPeopleEnterprises.PeopleID) ON tblNrYears.NrYearsID = tblPeopleEnterprises.NrYrsID
WHERE (((tblPeopleEnterprises.DateTo)<Date()));

*similar for tblPeopleEntities for the qryEntities
*and for tblPeopleVehicule for theqryVehicles.
So one should include the previous table(tblPeopleEntities, tblPeopleEnterprises,tblPeopleVehicule) like in the previous db ?

[mike60smart]

my apologies.

In the attached I have removed all unwanted tables.

I revised the query.

Look at the Relationships to see how all Allocations of Vehicles, Entities & Enterprises are now logged in 1 Table.

In the Sub for Allocation when you select an Allocation Type using the 1st Combobox the 2nd Combobox only lists the Allocations associated with the Type selected.

[mackson]

Hi!

In the Sub for Allocation when you select an Allocation Type using the 1st Combobox the 2nd Combobox only lists the Allocations associated with the Type selected.

It's a kind of cascading combobox, right ?.
By using allocation indistinctly for Vehicle, entity, Enterprise, the query returns all the allocations whose date has passed. I've updated the query to account only for expired clearance (vehicle) per agent as below

Can I follow he same logic to build qryEntity and qryterprise to extract people from the same entity or enterprise with vehicle date passed ?

[mike60smart]

I would not setup the criteria on Allocation but set the Criteria for "Type" as follows:-

Like * & [Enter Type required or Leave Blank for All Types] & *

This allows you to run the same query for a number of Results.

You can run the query to see All Allocations or just "Vehicles", "Entity" or "Enterprise"

[mackson]

I would not setup the criteria on Allocation but set the Criteria for "Type" as follows:-

Like * & [Enter Type required or Leave Blank for All Types] & *

This allows you to run the same query for a number of Results.

You can run the query to see All Allocations or just "Vehicles", "Entity" or "Enterprise"

Hi !
Thank you for your reply. I have two observations if you don’t mind:
1. Is there a way to have multiple choice in the query to select for “vehicle”, “Entity”, or “Enterprise” instead of not typing the values, which can induce a typing mistake:
2. can I correct what I mean by highlighting expired clearances with complete infos like name, surname, type of clearances date of expiry, etc ? :
· by company/Enterprise (If I select an Enterprise is it possible for the query to return all the people of that enterprise whose clearances are expired:name, surname, type of clearances date of expiry, etc)
· by entity (If I select an Entity is it possible for the query to return all the people of that entity whose clearances are expired:name, surname, type of clearances date of expiry, etc)
· per person: OK, I think the query is fine if ones select Vehicle
Regards

[mike60smart]

Hi
There is always a way in Access.

Google and look for "Query by Form"

You set up a Form with a number of Unbound Combobox's which select specific Lists of Items

You then create a Command Button with a VBA On Click event to filter your Form to only show the items you have selected.

[madpiet]

Q1: Use a form, and use a combobox when the user should select at most one value. use a multi-select listbox when you want the user to be able to select more than one option.


Q2: i would create a query to create a dataset and then base my report on that... Then I can pass whatever filter I want to the report when I open it. (Like "find me all the clearances that will expire before May" (you'd use an unbound form to collect the filter values and build the filter on the fly in the Click event of the button to open the report.


you can filter the report (to some degree) any way you want... if you can write a filter for it. <g>


You just build the "where clause minus the where statement" and pass it to the report in the DoCmd.OpenReport "reportname"...,strFilter
you'd just put the string variable containing the filter there.