service record database design approach

[complexx]

I'm trying to build a table structure for this database. Heres some background information about the business:

The business is a service business. We visit the customer's location and run tests on whatever water systems they have. Each customer is unique in that they could have any combination of systems at their site. They can also have more than one of the same type of system. The test results are the data that I need to record and store for future access. Each customer is visited on average once or twice a once a month. So there should not be more than 1-2 entries of data for each system for each customer per month.

For example
customer RUTGERS might have two systems labeled HWS and CHWS.
customer BMSQUIB might have three systems labeled HWS1, HWS2, and CHWS.

What I need to do with this information is go into the records of service visits, and retrieve for example, the last 4 visits of a specific system and prepare that information to be printed in a report along with their contact information.

I have come up with three tables to do this:

Customers Table contains:
Customer ID (pk), contact information data.

Systems Table contains:
System ID (pk), Customer ID, System Name

Service Records Table contains:
Record ID(pk), Date, System ID, Data

My thoughts were to have a table to contain customer information(each customer with a unique ID), a table to contain system information for each customer (each system has a unique ID), and a table to store the results of every service visit for each system(each individual visit has a unique ID)

Please critique this table design. If you think its sufficient, perhaps you could lead me in a direction pertaining to how to retrieve data on the most recent 4 visits (last 4 entries) for a specific SINGLE system from the Service Records table. I would assume that you would need to use a query and then get the data from that and put it into a form.

 

[neileg]

Looks like a good start. Don't use spaces in the field names, though.

A parameter query will allow you to specify which SystemID you want from the service records table. I would use a pair of cascading combo boxes on a form to allow you to select firstly the customer and then the exact system to provide the parameter for your query. If you only want the last 4 visits you need to use the TOP operator based on the date, in this case the top 4 records.

 

[complexx]

Ok. So I've setup a Form for the user to select the Customer and the System. I have linked that form to a query that retrieved all the results with the given information.

My current issue is that after I query for the first time and try to query a different set of results, it does not work unless I manually close the query results table. How can I reset the results of the query so that when I change parameters and click "get results" I don't have to manually close the query results table?

 

[neileg]

You need to requery the combo. Add some VBA code to the appropriate event on the form. The code will look like:

Me.myCombo.Requery

where myCombo is the name of the relevant control.

 

[complexx]

That makes sense, but I'm unsure as far as how to check if the query has already been opened.

I would assume you'd use some sort of "if" statement in the event code of the button I have created to run the query.

Something like:
if System History Query exists, then Me.System.Requery

But I'm not really sure how to code that.




On a side note, earlier you recommendeded using the TOP operator in order select the 4 most recent results. Would I just want to create a table based off my original query and only take the TOP 4 results from that query, then create a report and pass the data from that table into it??

 

[neileg]

That makes sense, but I'm unsure as far as how to check if the query has already been opened.

I would assume you'd use some sort of "if" statement in the event code of the button I have created to run the query.

Something like:
if System History Query exists, then Me.System.Requery

But I'm not really sure how to code that.

Just requery it anyway. Put this in the after update event of the system combo.

On a side note, earlier you recommendeded using the TOP operator in order select the 4 most recent results. Would I just want to create a table based off my original query and only take the TOP 4 results from that query, then create a report and pass the data from that table into it??

Why? Base your report on the query, not the table.

 

[complexx]

Ok, I couldn't get the requery to work but I found another somewhat crude method to accomplish what I want . Now to figure out how to get those results and toss in a report.

 

[neileg]

Now to figure out how to get those results and toss in a report.

Create the query, base the report on the query. When you open the report, it will run the query. If the query uses form controls as parameters, ensure that the form is open when the report runs.

 

[complexx]

Right now the query simply retrieves all results that match the system specified. I would like to keep it that way so I can use it for system review as well as data to put in a report. How do I use the TOP operator to specify I only want X number of results? Is it possible to do this TOP operation when the report is created as oposed to when the query is executed? or does it have to happen at the time of the query

 

[neileg]

You can set the Top Values property of the query in Design View using the box on the Toolbar (says All by default) or modify the SQL to, say:
SELECT TOP 5

If you want to alter the number of Top Values 'on the fly' you will have to build the sql in a string.

There's nothing to stop you having more than one version of the query and selct the one you want as appropriate.

 

[complexx]

Problem solved.