Between Dates Query on Multiple Fields

[mrenwick]

Hi All

I am relatively new to Access databases. I have a Database that tracks training completed by memeber of staff, some of this training has to be refreshed every 3 years. My table contains all the due dates for different types of training for each employee, so I tried to create a between dates query on all the fields, but this returns all employees where the critera is met on field one. Can anyone help please??

I also tried making a separate query for each field then a report to show the results of all of the queries. But I must have done something wrong as that did not work either.

Cheers
Mich

 

[Premy]

1. Create a report "rptShowTraining" designed to display the data u want to see/print.

2. Create a pop up form "frmShowTraining" with a combo that allows u to select the employee and 2 texboxes where the dates have to be entered. Create a button named "View" on the form and put this code behind it's click event:

Private Sub View_Click()

DoCmd.OpenReport "rptShowTraining", acViewPreview, "", _
"EmployeeID=Forms!frmShowTraining!cboEmployees AND DueDate Between Forms!frmShowTraining!Date1 And Forms!frmShowTraining!Date2"

End Sub

Selecting an employee, entering a start and an end date, then finally clicking the button, will open the report with just the due dates for that employee, contained in the specified interval.

HTH

 

[boblarson]

It sounds to me like you have a non-normalized database. Is that so? Do you actually have multiple date fields (one for each training)?

 

[mrenwick]

It sounds to me like you have a non-normalized database. Is that so? Do you actually have multiple date fields (one for each training)?

Yes
eg.

Name Tractor Forklift Crane Fire Safety Health & Safety
J Bloggs 25/11/08 05/12/08 17/01/09 27/03/09 22/02/09
A Smith 17/01/09 25/01/09 09/12/08 24/02/09 10/03/09

and so on. I want to be able to run one report/query that shows any training due next month.

 

[mrenwick]

1. Create a report "rptShowTraining" designed to display the data u want to see/print.

2. Create a pop up form "frmShowTraining" with a combo that allows u to select the employee and 2 texboxes where the dates have to be entered. Create a button named "View" on the form and put this code behind it's click event:

Private Sub View_Click()

DoCmd.OpenReport "rptShowTraining", acViewPreview, "", _
"EmployeeID=Forms!frmShowTraining!cboEmployees AND DueDate Between Forms!frmShowTraining!Date1 And Forms!frmShowTraining!Date2"

End Sub

Selecting an employee, entering a start and an end date, then finally clicking the button, will open the report with just the due dates for that employee, contained in the specified interval.

HTH

I don't fully understand what you mean. Also, I would like to run one report/query to show training due next month for all employees.

Will atempt your suggestion anyway.
Thanks
Mich

 

[boblarson]

Yes
eg.

Name Tractor Forklift Crane Fire Safety Health & Safety
J Bloggs 25/11/08 05/12/08 17/01/09 27/03/09 22/02/09
A Smith 17/01/09 25/01/09 09/12/08 24/02/09 10/03/09

and so on. I want to be able to run one report/query that shows any training due next month.

This is not normalized and therefore getting data out with a query or so is going to be like pulling teeth without any pain medication - very, very painful.

You really should redesign this to be properly normalized and therefore you can get reports very easily.

 

[mrenwick]

Sorry Bob

I have limited knowledge of Access, what is a normalised database ??

 

[boblarson]

Sorry Bob

I have limited knowledge of Access, what is a normalised database ??

Here's a good, not too long, explanation:
http://www.allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_03.pdf

 

[marleymanner1]

I have limited knowledge of Access, what is a normalised database ??

It is something that people who haven't screwed up their data already, have come to believe is what's "right". That is not so. There are plenty of people that don't know how to normalize data, and simply don't care, including myself. A database is made to make money, or something else that's useful. Normalization is not necessarily useful if it doesn't make you money. A customer is not going to stand around and wait to see if you can enter his record "properly". Personally, I wouldn't worry AT ALL about not having a normalized database, because there are people like me out there that can fix just about anything, or at least give you good advice on where to go to get things fixed when they go wrong.


And just to add to this, normalization is crap anyway, because in two years, it will be useless. Either that, or there will be a 5th normal form that some other weirdo has come up with. People creating different structures and having different opinions is what makes this world go 'round. You're just part of the pack bro.

 

[mrenwick]

So how do I get the data out that I want????

 

[MSAccessRookie]

It is something that people who haven't screwed up their data already, have come to believe is what's "right". That is not so. There are plenty of people that don't know how to normalize data, and simply don't care, including myself. A database is made to make money, or something else that's useful. Normalization is not necessarily useful if it doesn't make you money. A customer is not going to stand around and wait to see if you can enter his record "properly". Personally, I wouldn't worry AT ALL about not having a normalized database, because there are people like me out there that can fix just about anything, or at least give you good advice on where to go to get things fixed when they go wrong.

Normalization up front makes you money faster, because Queries, Forms, and Reports can be written much more efficiently against a normalized database. The more data you are handling, the greater the need for Normalization.

Lack of Normalization makes money for people like me when I am able to make processing better, faster, and more reliable by redesigning the methods of processing the data, including Table, Form, and Query structures.

 

[boblarson]

It is something that people who haven't screwed up their data already, have come to believe is what's "right". That is not so. There are plenty of people that don't know how to normalize data, and simply don't care, including myself. A database is made to make money, or something else that's useful. Normalization is not necessarily useful if it doesn't make you money. A customer is not going to stand around and wait to see if you can enter his record "properly". Personally, I wouldn't worry AT ALL about not having a normalized database, because there are people like me out there that can fix just about anything, or at least give you good advice on where to go to get things fixed when they go wrong.

BullSh#@

Normalization is important, and it is one of those things that helps with data integrity (something I'm sure you know nothing about marley) and elimination of redundant data. Yes, there are many people out there who never normalize and "get by" but at what cost? If you are wanting something that will require very little maintenance, will do the job, and you almost never have to redesign your tables, forms, reports, etc. to make something FIT, which is what you wind up doing if you haven't normalized.

So, your "advice" is like telling people not to wear helmets on motorcycles or wearing seatbelts. Yes, you can do those things, but eventually you may wind up suffering big time because of it.

 

[marleymanner1]

Lack of Normalization makes money for people like me when I am able to make processing better, faster, and more reliable by redesigning the methods of processing the data, including Table, Form, and Query structures.

Thus you should be thankful that people don't know how to do this stuff. Because when the typical businessman gets what he needs out of you, you're out of work. That's why I never want to work in IT. Number one - because it is not secure. And number two - because virtually anything that I can get from a supposed "IT wizard", I can get off the internet for free anyway.

BullSh#@

That's a great response Bob. I must admit that I would never need your services because I can all of this stuff myself. But hey, there ARE people out there I know that needs yours. I'm simply making my case to this OP, not picking a fight.

And BTW, nice selling mode speech.

 

[boblarson]

You're just part of the pack bro.

You'll have to come up with a more compelling argument that that sis.

 

[boblarson]

Thus you should be thankful that people don't know how to do this stuff. Because when the typical businessman gets what he needs out of you, you're out of work. That's why I never want to work in IT. Number one - because it is not secure. And number two - because virtually anything that I can get from a supposed "IT wizard", I can get off the internet for free anyway. That's a great response Bob. I must admit that I would never need your services because I can all of this stuff myself. But hey, there ARE people out there I know that needs yours. I'm simply making my case to this OP, not picking a fight.

And BTW, nice selling mode speech.

Sounds like you need to just go somewhere where people might actually care. This is not it.

 

[MSAccessRookie]

So how do I get the data out that I want????

Normalize the data, and the results will be easier to obtain, maintain and understand. Somethinbg like this would be a start:

tblEmployeeSkills
    EmpSkillsPK (AutoNumber)
    EmpID (Number - FK to tblEmployees)
    SkillID (Number - FK to tblSkills)
    CertDate (Date/Time - Last Certification Date)
 
tblEmployees
    EmpID (Number (AutoNumber?) - FK for other Tables)
    (Other columns as required)
 
tblSkills
    SkillID (Number (AutoNumber?) - FK for other Tables)
    (Other columns as required)

Using this structure, you can see how easy it will be to create a report or form to display a report pertaining to an employee and the status of their their skillset, as well as a report pertaining to a skillset and the status of the employees that have it.

This is a more normalized approach.

Feel free to continue as you are doing, as there are many here who can and will try to assist you. I would look into a more normalized approach.

 

[marleymanner1]

I understand Bob. I'm sticking around though, if you don't mind. And to show that I really do actually care, here is something for the OP:

You need this:

Table1
NameID = PK autonumber
Name = name of person

Table2
TrainingID = PK autonumber
TrainingName = forklift, crane, etc...(these types)
NameID = FK (ID of person in table 1)
Completion DATE = this is obvious



After you get this rearranged, you can build your query, and thus your report in a block style, just like u want. Query SQL will be something like this:

SELECT table1.nameID, table1.name, table2.trainingname,
  table2.completionDATE FROM table1
  INNER JOIN table2 ON nameID = nameID

that is an example of the code that will pop out if you just create those tables with the relationships that are specified above.

and BTW, the word "NAME" is reserved, so don't use it to "name" any field in a table. The reason is irrelvant, just don't.

 

[Rabbie]

It is something that people who haven't screwed up their data already, have come to believe is what's "right". That is not so. There are plenty of people that don't know how to normalize data, and simply don't care, including myself. A database is made to make money, or something else that's useful. Normalization is not necessarily useful if it doesn't make you money. A customer is not going to stand around and wait to see if you can enter his record "properly". Personally, I wouldn't worry AT ALL about not having a normalized database, because there are people like me out there that can fix just about anything, or at least give you good advice on where to go to get things fixed when they go wrong.


And just to add to this, normalization is crap anyway, because in two years, it will be useless. Either that, or there will be a 5th normal form that some other weirdo has come up with. People creating different structures and having different opinions is what makes this world go 'round. You're just part of the pack bro.

Normalisation actually makes my job easier because I can deal with the inevitable changes in requirements that users make. Normalisation has been here for more than 30 years so I think it will be around for a lot longer than you think.

BTW see this link or more informtion on 5th Normal Form

 

[MSAccessRookie]

Thus you should be thankful that people don't know how to do this stuff. Because when the typical businessman gets what he needs out of you, you're out of work. That's why I never want to work in IT. Number one - because it is not secure. And number two - because virtually anything that I can get from a supposed "IT wizard", I can get off the internet for free anyway. That's a great response Bob. I must admit that I would never need your services because I can all of this stuff myself. But hey, there ARE people out there I know that needs yours. I'm simply making my case to this OP, not picking a fight.

And BTW, nice selling mode speech.

And that is why I DO work in IT. Because of the number of Non-Technical people who can create quick database solutions that are unable to grow to any realistic extent, and need an expert to reorganize their application and make it able to move forward again.

I earnestly wish you good luck with the applications that you write and support, because in the long run you are going to need it.

 

[marleymanner1]

BTW see this link or more informtion on 5th Normal Form

LOL Rabbie! Thanks for everyone's insight, and I apologize for starting the argument. Facts are facts. I will get out. Let us let the OP figure it out now. I think we've done enough arguing. After all, I'm the new guy here. Take it easy on me. I'm inexperienced and stupid.

I earnestly wish you good luck with the applications that you write and support, because in the long run you are going to need it.

I'm sorry but I have to comment on this as well:

I suppose you think that the automakers CAN'T make it easier on consumers for gasoline? Sure they can. They keep it in check to control the rest of the world that takes things to EXTREMES. Normalization puts people out of jobs. Sorry, that's a terrible thing to say, but efficiency does that!