select query

[dichotomous]

Hi

Am hoping you can help.

I have a database that is a health and safety application. In it I can record training (with expiry), plant maint (with expiry) and a variety of other things for which expiry date is important.

I have a table called.....tblexpiry.

My plan is to run append queries from the plant table, training table so that I can populate the expiry table.

Expiry table is linked to a pop up reminder system; i.e; you have xyz training expiring etc.

Not all training we deliver has an expiry date. It may be an internal procedure or a National Unit standard that doesn't expire.

What i want is for my append query to only pick up records that have a date entered.

Would I build code for this, or enter parameter's into the query?

Just as an aside - I am happy with the structure of what I have designed; i.e; append queries to pick up expiry dates from the various tables - as, there are so many functions in Health and Safety that require a watch on expirey date.

Looking forward to hearing from any one who wants to contribute.

 

[plog]

Just as an aside - I am happy with the structure of what I have designed

Ha ha, that's a very telling sentence. As I read your description I kept thinking 'improper structure'. I was 65% certain that you don't have the right structure, then I came to that sentence and my confidence went to 95% certain its wrong.

Action queries (APPEND, MAKE TABLE, DELETE, etc.) are very big red flags in a database. Usually they are used to store calculated values or to 'move' data among tables--both of which are incorrect. Sounds like that's the case here.

Can you post your structure? A screenshot of the relationships table? A proper structure fixes and avoids a lot of issues that people don't immediately associate with a bad structure.

 

[arnelgp]

something like:

INSERT INTO tblExpiry (tblExpiry.field1, tblExpiry.field2, ....) SELECT table2.field1, table2.field2, ... FROM table2 WHERE NOT (table2.ExpiryDate IS NULL);

 

[dichotomous]

Ha ha, that's a very telling sentence. As I read your description I kept thinking 'improper structure'. I was 65% certain that you don't have the right structure, then I came to that sentence and my confidence went to 95% certain its wrong.


Can you tell me why action queries are red flags?

Sure, I will put up a table structure when I get to the office.

The Expiry table is stand alone. No relationships to other tables. I want it like that. I felf it better than having FKs for each of the originating tables where expiry dates occur.

 

[Galaxiom]

append queries to pick up expiry dates from the various tables - as, there are so many functions in Health and Safety that require a watch on expirey date.

You are trying to justify your ill-considered decision to denormalize the data. My advice is, don't do it.

The query you want to use to append duplicated data from other tables could just as easily be written as a select and used directly in your Expiry system.

 

[dichotomous]

You are trying to justify your ill-considered decision to denormalize the data. My advice is, don't do it.

do you have to use such damning rhetoric? the energy you've used to slam my idea could have been put into giving a bit more detail.

seriously mate. I asked a genuine question. If you need to pontificate, don't bother replying.

So, I will ask the same question another way.....

There's more than one table with expiry date. Sure, I can do a select query, but, the tables aren't all related. I.E; plant doesn't talk to training, and neither it should.

should each table (expiry) have its own pop up (run by or from a select query), or my original idea was for all expiry to converge in one table (obviously a no no).

Please enlighten.

 

[Galaxiom]

do you have to use such damning rhetoric? the energy you've used to slam my idea could have been put into giving a bit more detail.

seriously mate. I asked a genuine question. If you need to pontificate, don't bother replying.

It was reasonably clear from your original post that you were aware you were likely to get some criticism for your design and were dismissing it out of hand in anticipation. I was simply trying to get across that you should back down because you were heading in the wrong direction.

I wouldn't mind a dollar for every time a poster has claimed they have properly structured data and turned out to be wrong. Indeed such claims are almost invariably a flag that indicates such an error which is what plog was intimating.

When I first read your post I elected to say nothing because I didn't want to get into a debate and I hoped someone else would take that on. I decided to post when I saw the answers you were getting were not very helpful.

There's more than one table with expiry date. Sure, I can do a select query, but, the tables aren't all related. I.E; plant doesn't talk to training, and neither it should.

Tables need not be related to return data in the same query. Use a UNION ALL query to return the information you require from each table.

Basically:

SELECT "Training" AS ExpiryType, somefield, ExpiryDate
 FROM tblTraining
 WHERE ExpiryDate Is Not Null
UNION ALL
 SELECT "Plant", somefield, ExpiryDate
 FROM tblPlant 
 WHERE ExpiryDate Is Not Null
UNION ALL
 SELECT etc etc

Each subquery can be constructed to return whatever fields you want. I expect you would have to join another table to get all the details.

All subqueries must return the same number of fields and datatype. The first subquery defines the field names. The subsequent field names are ignored. Any Order By clause will only be processed at the end.

You can use this query as the basis of your Expiry date system. Note however that Union queries are not Updateable.

BTW. If you are storing the TrainingDate and the ExpiryPeriod you should not be storing the ExpiryDate. Instead it should be calculated from the other two fields every time it is required.

Only store the results of calculations if they are massive and the time required to process is too long. This situation is quite rare because database engines are extraordinarily fast if the data is properly structured and indexed.

 

[dichotomous]

Sorry. Been away. Have just read your reply (Glaxiom). Thank you for taking the time.
I didn't know that you could return data in a query from unrelated tables.

I will let you know how I get on.

Again, thank you.

 

[dichotomous]

BTW. If you are storing the TrainingDate and the ExpiryPeriod you should not be storing the ExpiryDate. Instead it should be calculated from the other two fields every time it is required.

Only store the results of calculations if they are massive and the time required to process is too long. This situation is quite rare because database engines are extraordinarily fast if the data is properly structured and indexed.

Training date is the date that the training occurred on. Expiry is not a calculated field however. So, not quite sure what you mean.

I.E; a first aid cert lasts for 2 years. The expiry date is mandatory. Some Unit Standards have expiry dates as well such as Powered Lift Trucks, or Elevated Work Platforms.

I don't know what you meant by expiry period and expiry date........

 

[dichotomous]

It was reasonably clear from your original post that you were aware you were likely to get some criticism for your design and were dismissing it out of hand in anticipation. I was simply trying to get across that you should back down because you were heading in the wrong direction.

I just wanted to say thank you for re-directing me.

Union queries. I knew nothing about. Now I do. You have helped me immeasurably.

Best regards
Alastair.

 

[Galaxiom]

Training date is the date that the training occurred on. Expiry is not a calculated field however. So, not quite sure what you mean.

I.E; a first aid cert lasts for 2 years. The expiry date is mandatory. Some Unit Standards have expiry dates as well such as Powered Lift Trucks, or Elevated Work Platforms.

I don't know what you meant by expiry period and expiry date........

I expect you would store information about the Unit Standards and Training in a table with a field for the time that the certification applies for.

So if you record the first aid cert as 2 years and you record the TrainingDate, recording the Expiry date would mean you are recording something that could be calculated from the other two values. This is a breach of Normalization principles because it then becomes possible to record conflicting information.

Your query should join the training record to the certification information to calculate the Expiry date.

 

[dichotomous]

I expect you would store information about the Unit Standards and Training in a table with a field for the time that the certification applies for.

So if you record the first aid cert as 2 years and you record the TrainingDate, recording the Expiry date would mean you are recording something that could be calculated from the other two values. This is a breach of Normalization principles because it then becomes possible to record conflicting information.

Your query should join the training record to the certification information to calculate the Expiry date.

I understand I think. So, expiry period is a value i would enter instead of storing the actual expiry date. And, the query would actually calculate the expiry date (not store it).

Say: Date_Completed is a stored value
Expiry_Period - is a user entered value (say 2 years, or just 2).

Then the query would calculate

Date_completed and Expiry Period to give you the actual expiry date. All that being said; is it a Dateadd(...................), i assume it would be.

And, can you help me with the syntax, because I've goolged a bit and cant quite find the needle in the stack.

Cheers.

 

[dichotomous]

Galaxiom.

I have now gone through my application and removed any stored dates for expiry. Expiry dates now appear on all my sub forms (based on queries) with calculated dates. I had to go into the tables and remove the expiry fields and replace with an expiry period. The user can select this based on the type of certification.

I also found the syntax I needed. Expiry: DateAdd("yyyy",[Expiry_Period],[Date_Completed])

So, thank you once again for your help. I know you put a bit of effort into it.

Rgds.

 

[dichotomous]

I expect you would store information about the Unit Standards and Training in a table with a field for the time that the certification applies for.

So if you record the first aid cert as 2 years and you record the TrainingDate, recording the Expiry date would mean you are recording something that could be calculated from the other two values. This is a breach of Normalization principles because it then becomes possible to record conflicting information.

Your query should join the training record to the certification information to calculate the Expiry date.

Please can you tell me. Is it possible to calculate part years using the DateAdd function

I have set up my Expiry_Period feild in decimals in the hope that entering .5 would give me half a year.

eg: DateAdd("yyyy",[Expiry_Period],[Date_Completed])

Alas, no result. I perhaps could use DateAdd("m",[Expiry_Period],[Date_Completed])

and instruct my users to enter 6 or 18 or 3.

What is the best approach?

Regards

 

[arnelgp]

i think months would be easier to calculate rather than fraction of a year.