Why won't a query show results (1 Viewer)

Design by Sue

Registered User.
Local time
Today, 15:31
Joined
Jul 16, 2010
Messages
816
I am creating an append query based on a form. I have results in 3 of the fields but for DateCompleted and Completion Time the fields are blank when I view the query. I am totally stumped as to why this is happening. Has anyone had this experience and can you point me to what might be causing it. I have double and triple checked the names of the controls (I know there may be some there that will go against the grain for naming but that is what I have been given to work with and don't want to change them and cause other problems) Date completed is bound and Completion Time is unbound. Here is the SQL.


INSERT INTO [SOP Time Table] ( [SOP Number], SOPVersion, DateCompleted, [Employee Number], CompletionTime )
SELECT [Forms]![Add by SOP FRM]![ComboSOPNo] AS SOPNumber, [Forms]![Add by SOP FRM]![Text85] AS Version, [Forms]![Add by SOP FRM]![DateCombo] AS DateCompleted, [Forms]![Add by SOP FRM]![ComboEmpName] AS EmpNumber, [Forms]![Add by SOP FRM]![Completion Time] AS completionTime
FROM [SOP Time Table];

I know this is so easy for many of you but this is not easy for me and I have to get this completed so if I made a mistake in my wording please forgive me and if this seems like a stupid question, again forgive me. I am doing the best I can to ask the question. Thanks.
 
Hopefully this will help, when I tried to run the query I got this error:
 

Attachments

  • Screen Shot 2024-06-10 at 1.05.03 PM.png
    Screen Shot 2024-06-10 at 1.05.03 PM.png
    5.8 KB · Views: 83
One of your form fields does contain content that cannot be converted to the required data type of the table column. Most likely a date value that is not a proper date.
 
One of your form fields does contain content that cannot be converted to the required data type of the table column. Most likely a date value that is not a proper date.

Would this cause it to not show up in the query? and I can't figure out how to fix it (again please forgive my ignorance) Thanks
 
I am creating an append query based on a form. I

I have to question this whole process. Why isn't the form bound to [SOP Time Table] and let Access do the heavy lifting when adding records? What does manually doing an APPEND allow you to do that a bound form won't?

With that aside, your APPEND is very weird itself. Why do you have a SELECT query in the APPEND that doesn't SELECT any fields from the table in the FROM? All the SELECT fields are fields in the form.

I really think you are traveling down the wrong path with this. The APPEND syntax isn't the issue, the methodology itself is incorrect.
 
Also, I think you should fix some things with your table:

1. DateCompleted and Completion Time shouldn't be seperate fields. It's called a Date/Time field because it can accomodate a date and a time in the same field. So those 2 fields should become one.

2. Poor names. You should only use alphanumeric and underscore in table and field names. Spaces and other special characters only make coding and querying more difficult later on.
 
I have to question this whole process. Why isn't the form bound to [SOP Time Table] and let Access do the heavy lifting when adding records? What does manually doing an APPEND allow you to do that a bound form won't?

With that aside, your APPEND is very weird itself. Why do you have a SELECT query in the APPEND that doesn't SELECT any fields from the table in the FROM? All the SELECT fields are fields in the form.

I really think you are traveling down the wrong path with this. The APPEND syntax isn't the issue, the methodology itself is incorrect.
It is bound to another table.

As far as the query, I used Access to create it and that is what came up.

And yes I am sure my methodology is wrong as I am up to my neck in confusion at this point!
 
Also, I think you should fix some things with your table:

1. DateCompleted and Completion Time shouldn't be seperate fields. It's called a Date/Time field because it can accomodate a date and a time in the same field. So those 2 fields should become one.

2. Poor names. You should only use alphanumeric and underscore in table and field names. Spaces and other special characters only make coding and querying more difficult later on.
The date completed is the actual date completed (duh) The completion time not the same in that it keeps track of the time it took to complete the training, such as 20 minutes. I know the naming is screwy but in part that is what i have to work with because this is an extensive database and I can't start changing these things now.
Appreciate your thoughts
 
It is bound to another table.

So does that mean you are storing the same data in multiple places?

If you know the values that need to go into your table, you don't use a SELECT to put them there, you simply construct your INSERT INTO query with those values. Here's a good resource for constructing such a query:

 
Would this cause it to not show up in the query? and I can't figure out how to fix it (again please forgive my ignorance)
Your query is an Append Query (INSERT INTO ....). It does not show anything. Instead it inserts new records into the target table.
This whole approach looks suspicious to me. @plog elaborated on this in more detail already.
 
Ok so trying to back up and look at this differently. Currently the database stores the Employee ID, SOP Number, SOP Version and Date Completed in the Main Table. This only stores the most current training for any SOP done by each Employee. So employees many times have to repeat training each year or at a specific schedule. There has been no need to keep track of previous completed trainings. BUT now they what to add the ability to track the time to complete the training for every SOP that is completed and in the end be able to out put a report of this to excel. I know I could add a completion time to the Main Table but that would not store the history of all trainings. I know the plan was not correct but I thought maybe the easy way out would be to create a table that included all of the information that was needed for this excel output but obviously I am going down a blind alley and not getting anywhere on this. (yes I know information sould not be stored twice but in my simple mind I thought this would just be easier) Any thoughts? If I create a table that records the SOP Number, the date completed and the time to complete, I could query on both tables but because the Main table does not keep track of previous trainings, the employee results would only display for the most current training and the user wants to include that info. (This is why I thought a table that kept track of all of these in one would be the easiest way to go)

I really can't go back and update the existing tables to keep track of the expired training. I need to do something to add to what already exists.

As I have stated in previous posts, my brother used to help me with this work but he passed away a year and a half ago and I am floundering here to get this job done. Please forgive my lack of knowledge!
 
I think the best way to communicate/solve this is to work backward from what you expect to end up with. I suggest you mock up the Excel file you expect to be the end product. You can use fake data but make it as logical as possible and post it here.

With that we can help you determine the tables you need to achieve that final result. After that we can help with the queries needed to generate the data you expect to end up with. And then finally, as is the last step in all databases, we can help you work on the forms to input/interact with the data that goes into those tables.
 
I think the best way to communicate/solve this is to work backward from what you expect to end up with. I suggest you mock up the Excel file you expect to be the end product. You can use fake data but make it as logical as possible and post it here.

With that we can help you determine the tables you need to achieve that final result. After that we can help with the queries needed to generate the data you expect to end up with. And then finally, as is the last step in all databases, we can help you work on the forms to input/interact with the data that goes into those tables.
Thank you - I will be working on this but will be out of the country for a couple of weeks so won't be able to get back to until July but I would really appreciate your help!
 
I think the best way to communicate/solve this is to work backward from what you expect to end up with. I suggest you mock up the Excel file you expect to be the end product. You can use fake data but make it as logical as possible and post it here.

With that we can help you determine the tables you need to achieve that final result. After that we can help with the queries needed to generate the data you expect to end up with. And then finally, as is the last step in all databases, we can help you work on the forms to input/interact with the data that goes into those tables.
I am able to create the output I need using the Main Table,the only issue is that that records only the current training and does not keep track of previously completed classes. It is this past history that is the problem. I know I need a separate table but a having problems addition the information from the form to this new table (this is where I was in the beginning of this post but I now realize I only need to save the SOP Number, Date Completed and the Time to Complete in that table for all of the training. Maybe a night's sleep will reveal the answer. I am back to the problem of trying to append the information to a second table, one that is not bound to the form. And the fact that I can't get a query to display the Date Completed and Time to Complete info, I am still stuck. I have contacted the user, if they are ok with only current training being recorded as it is for the rest of the database I have the solution but if they insist on this previously completed training I am back in the weeds.
 
The usual way to do this is to include dates in the main table and then display selected results by a WHERE clause that includes

"... AND (TrainingDate > DateAdd( "yyyy", -1, Now() ) ) AND ... "

or something like this. What I showed you would give you all training within the last 365 days. But you could do something else.

You WOULD NOT create a separate table from the main table. You would simply assure that the training event had a date on it and use the date as a filter.
 
I have a database application that pretty much does this.
Ok so trying to back up and look at this differently. Currently the database stores the Employee ID, SOP Number, SOP Version and Date Completed in the Main Table. This only stores the most current training for any SOP done by each Employee. So employees many times have to repeat training each year or at a specific schedule. There has been no need to keep track of previous completed trainings. BUT now they what to add the ability to track the time to complete the training for every SOP that is completed and in the end be able to out put a report of this to excel. I know I could add a completion time to the Main Table but that would not store the history of all trainings. I know the plan was not correct but I thought maybe the easy way out would be to create a table that included all of the information that was needed for this excel output but obviously I am going down a blind alley and not getting anywhere on this. (yes I know information sould not be stored twice but in my simple mind I thought this would just be easier) Any thoughts? If I create a table that records the SOP Number, the date completed and the time to complete, I could query on both tables but because the Main table does not keep track of previous trainings, the employee results would only display for the most current training and the user wants to include that info. (This is why I thought a table that kept track of all of these in one would be the easiest way to go)
So you have a table of employees with information about employees. It would have an EmployeeID primary key.

Then you need a table of courses. This is generic information about a course and not specific information about an offering of a course.
This includes how long until you are required to renew the training (expiration) and if the course is a mandatory or optional. In your case I assume this is a generic table of SOPs with and SOP_ID instead of a training course ID

T_TrainingCourses

TrainingCourseIDTraining_TitleSponsoring_OrganizationType_TrainingRequired_TrainingExpirationInYears
386​
Food Handler TrainingChautauqua Cty DOH & Human ServicesClassroom
No​
5​
468​
NYS Basic EMTNYS DOHClassroom
No​
4​
298​
TSG - GOLD Interrator Reliability - Preschool/UPKTeaching StrategiesOnline
No​
3​
348​
TSG - Interrater Reliability -Infants/ToddlersTeaching StrategiesOnline
No​
3​
357​
MAT - RenewalPDPOnline
No​
3​
Now I need a table to hold when an employee took the course. This is specific information about a course offering and in your case include how long it took to complete
This is called a junction table and allows you to create a Many_to_Many relationship linking a Course (SOP) to an Employee and on a given date.

T_Employees_TrainingCourses

ETCC_IDEmployeeID_FKTrainingCourseID_FKTime_To_CompleteCourseDateCertificate
67​
15​
42​
11/19/19​
Yes​
68​
15​
42​
05/09/19​
Yes​
69​
16​
46​
01/16/20​
No​
72​
15​
48​
08/11/20​
Yes​
96​
5​
80​
02/01/19​
No​
103​
6​
91​
09/04/19​
Yes​
105​
5​
91​
09/04/19​
Yes​
124​
15​
120​
06/03/20​
Yes​
127​
5​
124​
08/30/19​
Yes​
You can see in this snapshot that employee 15, 5, 6 took course on certain dates and some of these offerings provided a certificate

Using this simple organization I can show all completed training, as well as current training, expired training.
T1.png



T2.png



what i have to work with because this is an extensive database and I can't start changing these things now
That is wrong. You cannot just plow forward with a broken design if you need to meet your objectives. Your database is not correctly designed so fix it now or you will be wrestling it with complicated workarounds for here on out.
 
The usual way to do this is to include dates in the main table and then display selected results by a WHERE clause that includes

"... AND (TrainingDate > DateAdd( "yyyy", -1, Now() ) ) AND ... "

or something like this. What I showed you would give you all training within the last 365 days. But you could do something else.

You WOULD NOT create a separate table from the main table. You would simply assure that the training event had a date on it and use the date as a filter.
But how do I keep track of previous trainings that are not included in the Main Table?
 
But how do I keep track of previous trainings that are not included in the Main Table
By applying the design, I showed.
 
I have a database application that pretty much does this.

So you have a table of employees with information about employees. It would have an EmployeeID primary key.

Then you need a table of courses. This is generic information about a course and not specific information about an offering of a course.
This includes how long until you are required to renew the training (expiration) and if the course is a mandatory or optional. In your case I assume this is a generic table of SOPs with and SOP_ID instead of a training course ID

T_TrainingCourses

TrainingCourseIDTraining_TitleSponsoring_OrganizationType_TrainingRequired_TrainingExpirationInYears
386​
Food Handler TrainingChautauqua Cty DOH & Human ServicesClassroom
No​
5​
468​
NYS Basic EMTNYS DOHClassroom
No​
4​
298​
TSG - GOLD Interrator Reliability - Preschool/UPKTeaching StrategiesOnline
No​
3​
348​
TSG - Interrater Reliability -Infants/ToddlersTeaching StrategiesOnline
No​
3​
357​
MAT - RenewalPDPOnline
No​
3​
Now I need a table to hold when an employee took the course. This is specific information about a course offering and in your case include how long it took to complete
This is called a junction table and allows you to create a Many_to_Many relationship.

T_Employees_TrainingCourses

ETCC_IDEmployeeID_FKTrainingCourseID_FKTime_To_CompleteCourseDateCertificate
67​
15​
42​
11/19/19​
Yes​
68​
15​
42​
05/09/19​
Yes​
69​
16​
46​
01/16/20​
No​
72​
15​
48​
08/11/20​
Yes​
96​
5​
80​
02/01/19​
No​
103​
6​
91​
09/04/19​
Yes​
105​
5​
91​
09/04/19​
Yes​
124​
15​
120​
06/03/20​
Yes​
127​
5​
124​
08/30/19​
Yes​
You can see in this snapshot that employee 15, 5, 6 took course on certain dates and some of these offerings provided a certificate

Using this simple organization I can show all completed training, as well as current training, expired training.
View attachment 114460

View attachment 114459



That is wrong. You cannot just plow forward with a broken design if you need to meet your objectives. Your database is not correctly designed so fix it now or you will be wrestling it with complicated workarounds for here on out.
Yes the database has other tables that keep track of the training courses and other info. I am only mentioning the Main table here because to me it makes the most sense to keep track of the Time to Complete except for the fact that it only holds current training. I actually have everything working as i need to using the main table except for previous training. (thanks for your extensive reply! aI appreciate the time you put into it)
 

Users who are viewing this thread

Back
Top Bottom