How to list records that aren't there yet?

[Steve@trop]

I'm new to query design. I have a database where operators will enter readings from 14 different compressors, twice per shift, three shifts per day. In the Rounds table for each record the following fields are included:

Timestamp (includes date)

CompressorID (1-14)

Shift (1st, 2nd, 3rd)

Round (each set of readings is a round, each shift is supposed to do 2 per shift, the rounds are numbered Rnd 1 and Rnd 2)

Status (this is one of many readings they will record for each round/machine)

On the paper form it is easy for the operator to tell which machine they haven't recorded a round for yet because the space for it on the form is blank. In the Access form they are simply presented with a blank space to enter a new record. I'm trying to develop a strategy to make it easier for the operator to see at a glance which compressors they haven't entered readings for yet. Management also needs a weekly report of which rounds were done and which were missed.

I'm thinking of creating a datasheet from a query that will list the missing rounds for each compressor/round/shift based on the current date. I think I can put that datasheet onto the form the operator is using to record the readings or perhaps on a subform they can access with a button. I'm having trouble designing the query. So far I've created a query that lists all the rounds for a specific date, then another query that uses those results to list the compressors that are missing a record. I'm thinking that I'm making it too complicated than it needs to be. I'm hoping someone here will have a better strategy.

Thanks in advance,

Steve

 

[RainLover]

If CompressorID was in a separate table along with some further information about the compressor. eg Location.

You could the create a query that includes the original Table and this new one.

This time there will be a join. A thin line between the two tables. Right click on the line and then choose show all records from compressor and only those that match in the other.

You will need to test this and change a few thing if it does not work.

I feel that you currently have One Table instead of two or even three.

I think you need a separate Table for Shift but without seeing your Relationships diagram I can't be sure.

Your design of your tables may not be normalised.

 

[Steve@trop]

I'm sorry I didn't mention that I do have these other tables:

Compressor
Round
Shift

The data entry form uses a lookup to populate those text fields into the CompressorRounds table.

Thanks for the reply, I'll experiment with the join.

Steve

 

[Steve@trop]

It lists all the records in the table but doesn't produce a list of compressors with no record. I think the problem is the query will only return records that exist. I don't know if this will help or not but the question I want to ask Access is:

For this date, shift and round, which of the 14 compressors have no record?

Once I figure out how to ask that question in a query format and get results I'll be on my way to getting this done!

I just thought of something, perhaps I'm using the wrong type of query? I'm thinking a select query might not be what I want here.

I'm off to do a little research....

Thanks,

Steve

 

[Steve@trop]

I have a strategy that may work. I've just got to work out the details. What I'll probably have to do is calculate the amount of time between the last reading and the present time for each compressor. If it has been longer than say 3 hours since the last reading on a compressor, then the query will indicate that it hasn't been done yet. I've just got to nail down the best time period to use. As long as there has ever been a reading done on every compressor, I won't have the problem of trying to select records that don't exist yet. Perhaps I'll use conditional formatting to indicate a range of time lapses. For example, if it's been 4 hours it will show in red, less than that would be yellow.

For management reporting purposes, I'll do a count for each machine, shift, round combination for the time period they are interested in.

 

[RainLover]

If your strategy does not work may I suggest that you make a mock up Database and post it.

Just need your tables of concern and any query your have built. Naturally delete any sensitive data.

Please post in Access 2003.

 

[Steve@trop]

The checklist solution works but is not sufficient. I've created a sanitized and condensed version of my database in mdb format and it is attached. I have lots of other equipment types in my real database but they are all structured the same way. The form has a checklist button that pulls up the checklist solution.

Once I find a good solution for this, I'll be applying it to the other equipment in this database as well as another database I'm working on for another area.

Anybody got any good ideas?

Thanks,

Steve

 

[JHB]

It would set it up side by side, like shown it the attached picture, (run query2 in the database) then it is easy to see what is missing.
The you can build a form where you can select a date/shift or whatever.

 

[RainLover]

Steve

I would think that the Compressor Name and its details (Things that don't change) should be in a table of its own. Then the readings that are taken during the shift should be in a separate Table. These Tables should be related via the Primary key in Compressor Table to a Foreign Key in the Readings Table. The Primary Key should be Autonumber and the Foreign Key should be an Integer.

This rule should be applied to all your tables, which is currently not the case.

I am attaching two papers. One on Naming Conventions and the other is on Normalisation. In Normalisation you only need to know (for now) up to 3nf which is 3rd Normal Form.

I really can't help much more with your current problem until a few things are sorted out. Also if you don't attend to the Naming and Normalisation issues you will have a constant stream of coding issues.

Please post back when you need to ask Questions. Will try and help.

 

[Steve@trop]

Thanks Rain,

I don't see much problem with my tables naming-wise. I may not have used the conventional standard naming scheme but it is consistint throughout. All the tables end with a T, all the forms end with an F, the query's end with Q, etc. All the controls on the forms also follow a naming convention, they all start with the type of control (txt, cbo, etc).

The primary keys may be a problem. I didn't realize that they are supposed to have a unique name in the database. I named them all the same (HomeKey) and used an autonumber datatype. In some cases, I could have used one of the existing fields as the primary key but I simply added the HomeKey/autonumber field to all of the tables to be sure they all had a primary key. If I change the name of the primary keys, will it affect anything I've done so far?

I'm confused on the foreign key concept. In Access, do you specify a foreign key by manually creating a relationship? Is a foreign key a field in the table or just a relationship to another table field? When I look at the relationships I can see that many have been created automatically by Access and they do make sense.

I can also see that I have some reduntant data in my Rounds table. What I don't see is how I'm supposed to know which set of readings is for which compressor/round/shift/etc without having the compressor name in the Rounds table. Perhaps I need to have a query that pulls them all together and use that. If I need to fix this, I'll need to do it for ten more equipment types besides compressors!

Thanks again for your help!

Steve

 

[Steve@trop]

Thanks JHB, this is great so far,

For each date/round combination is there a way to list all the compressors and put an indicator on the ones that have no readings. For example, on May 2nd, the only readings are for CMP#5. Is there a way to have the query include a row for each of the rest of the compressors that have no readings for that date/shift/round combination? These rows could be blank or perhaps return text like "no data".

Steve

 

[RainLover]

Thanks Rain,

I don't see much problem with my tables naming-wise. I may not have used the conventional standard naming scheme but it is consistint throughout. All the tables end with a T, all the forms end with an F, the query's end with Q, etc. All the controls on the forms also follow a naming convention, they all start with the type of control (txt, cbo, etc).

Steve,
Naming conventions is not a rule. You can uses what you like however for your own benifit whatever you use stick to it. I did not notice the t after the table names. There are some situations where Access displays both Tables and Queries. It is benifical in thes curcumstances to start with a t or tbl and q or qry. This simply gathers like types together.

The primary keys may be a problem. I didn't realize that they are supposed to have a unique name in the database. I named them all the same (HomeKey) and used an autonumber datatype. In some cases, I could have used one of the existing fields as the primary key but I simply added the HomeKey/autonumber field to all of the tables to be sure they all had a primary key. If I change the name of the primary keys, will it affect anything I've done so far?

It is nice to look at a PK and know which table it is from. Also when you look at the FK you know which table it relates to. I did not think you joined Tables exclusively by the Autonumber which you should. Many people will say that a Natural key can be used. Such as EmployeeID or SSN etc. They are right but not always is one available so I simply stick with Autonumber. I do not know if changing your primary key will cause problems or not. This depends on the Foreign Key. If that field is populated correctly then you won't have a problem with the tables and relationships. You may have some problems with existing queries and forms etc. Nothing that can't be fixed.

I'm confused on the foreign key concept. In Access, do you specify a foreign key by manually creating a relationship? Is a foreign key a field in the table or just a relationship to another table field? When I look at the relationships I can see that many have been created automatically by Access and they do make sense.

All relationships are created manually. The important thing to do is to Enforce Referential Intregity. This ensures that you can't create a Foreign key unless there is a matching primary key. Do not check either of the two Cascase options unless you absolutely know what you are doing.

I can also see that I have some reduntant data in my Rounds table. What I don't see is how I'm supposed to know which set of readings is for which compressor/round/shift/etc without having the compressor name in the Rounds table. Perhaps I need to have a query that pulls them all together and use that. If I need to fix this, I'll need to do it for ten more equipment types besides compressors!

In the Master table (Usually the Main Form) you have the Primary key and its details. Lets say the value of the pk is 101.

In the Child table (Usually a sub form) you use the FK and enter 101. You can enter as many as you like. Hundred or more if you like.

There is a relationship in the properties that joins the master to the child. So when a new record is creates the FK is automatically populated.

Now you have one description for a particular piece of equipment which has many related readings, If you want to know about the equipment from the readings just follow back to the PK.

Post back with what I left out.

 

[Steve@trop]

Thanks Rain,

I don't think you left anything out.

I'm having a little trouble understanding that last bit though:

In the Master table (Usually the Main Form) you have the Primary key and its details. Lets say the value of the pk is 101.

In the Child table (Usually a sub form) you use the FK and enter 101. You can enter as many as you like. Hundred or more if you like.

There is a relationship in the properties that joins the master to the child. So when a new record is creates the FK is automatically populated.

Now you have one description for a particular piece of equipment which has many related readings, If you want to know about the equipment from the readings just follow back to the PK.

I've included the rounds form in the attached version of the database. How would I change it to fit what you suggest?

Thanks,

Steve

 

[RainLover]

Steve

I will create a simple Database as an example.

It will be much later today. Busy right now.

 

[JHB]

Thanks JHB, this is great so far,

For each date/round combination is there a way to list all the compressors and put an indicator on the ones that have no readings. For example, on May 2nd, the only readings are for CMP#5. Is there a way to have the query include a row for each of the rest of the compressors that have no readings for that date/shift/round combination? These rows could be blank or perhaps return text like "no data".

Steve

Yes it is possible, I've made a quick solution, (I think it is possible to reduce it, it is up to you).
Run first "Query2" then "AllCompr"

 

[RainLover]

Thanks Rain,

I don't think you left anything out.

I'm having a little trouble understanding that last bit though:

I've included the rounds form in the attached version of the database. How would I change it to fit what you suggest?

Thanks,
Steve

Steve

I put together a VERY rough form and subform. This is only a demonstration of how the Primary Key gets entered into the subform.

The structure has very little to do with what you are doing it is just to Demonstrate the PK and FK situation.

As a BTW do not use Memo field unless you really need it. Use Text with 255 characters. Some say a Memo field is prone to corruption.

Hope this helps.

Also this has nothing to do with the other advice you are getting.

 

[Steve@trop]

Thanks,

I took a look and it explains alot. I can see that the FK is an actual field in the table but has a relationship with the PK field in the other table. I have to take a longer look to figure out how the forms work differently.

Thanks again,

Steve

 

[RainLover]

I apply the rule that the Primar keys are only there as a unique identifier. I never show them to the user yet they are used a lot in the background.

The Foreign key identifies which record in the Master that relates to the current record in the Child table.

I never use composite Keys and I never use Natural keys.

The Relationship Window is a graphical representation.

Until you fix your structure you will have one problem after another. This includes Normalisation.

 

[Steve@trop]

Thanks JHB,

I think this will work. It's a bit complicated (3 queries and a scratch table)but it gets me what I want. It took me a while to follow along with what you did but I finally figured out how it all works (and learned a few things in the process). I've adapted it to what I need and I'll apply it to the other pieces of equipment. Some of them only get one reading per shift which makes them simpler. I made a form from the final query which lists all the compressors and has a "checkbox" under each shift/round combination. The checkbox isn't really a checkbox, it's actually a textbox that I've set with conditional formatting to fill in red when empty and green when it has a date in it. Screenshot is attached.

Thanks for your help!

Steve

 

[Steve@trop]

In this situation I'll need to run 3 queries, the Date Query (select), Query 1 (select) and Query 2 (Make Table). Query 2 depends on data from Query 1. I'm planning to run all of this from one button. Do I need to run just Query 2 or will I need to run the other queries also?

I guess what I'm asking is does a query refresh when another query or form calls for data from it?

Yes, I do plan to change the names of the queries.

Steve