Hi, I am planning a test request, process and report system. It goes like this, a customer requests a set of tests to be carried out on a particular item; the tests may be medical or mechanical or anything else. The point is each request is given a job number and each job number may contain a request for many tests.
Instead of a table containing many entries of job numbers
ie.
job 001 test1 result
job 001 test2 result
job 001 test3 result
job 001 test4 result
job 001 test5 result
job 001 test6 result
......etc
job 002 test1 result
job 002 test2 result
....etc
would I be better of generating a new results table for each job.
ie.
Table job001
=============
test1 result
test2 reult
etc
I am thinking that each job having its own table is maybe easier to archive or delete a complete job record etc
Also possible to email out a link to a particular job table if required by the customer!
Thanks for looking.
4 1435 nico5038 3,080
Recognized Expert Specialist
Guess I need to point you first to our article about normalization: http://bytes.com/topic/access/insigh...ble-structures
In this case I would create a tblJob with the JobNumber and e.g. the customer.
Next a tblTest can be created with the JobNumber (for the link) and the testnumber.
Finally, when there are "fixed" sets of tests you could create a tblTestTemplate holding one or more templates to insert a set of testrecords into the tblTest.
Getting the idea ?
Nic;o)
Hi, I already have my tables set up, I was just wondering if it may be a good idea to dump individual jobs to their own tables and not keep them all in one large results table. I suppose I could just generate a report and email that or a link to it to the customer.
My main reason for the question is because I am planning to move this project from access to a standalone vb app.
Tables I have are.
TEST SYSTEM STRUCTURE
=============== =============
TESTS
======
testid as string ' eg \001\ or \bno\
testname as string ' name or abbreviation of test
category as string ' eg \001\ or bno\ same id system as testid select from linked list
description as string
upper as string ' measure result or upper bound
lower as string ' mearure result or lower bound
normal as string ' typical result
units as string ' unit of measure
CATEGORY
===========
catid as string ' same id format \??\
category as string ' name of category
description as string ' description
PROFILES
=========
profid as string ' same id format \??\
teststring as string ' contains id's of all included tests in profile
CLIENT
==========
info on who requested the test
client as string ' could be department name, company name or individual
costcode as string ' id of customer
CLIENTDETAIL
==============
clientid as string ' some identifier
intextn as boolean ' internal or external customer
title as string ' mr, mrs, miss, dept
fname as string
sname as string
company as string
dept as string
position as string
address1 as string
address2 as string
town as string
county as string
postcode as string
tel as string
email as string
JOB
=====
detail of the requested job
client ' id of client, lookup client id
requestedby as string ' who signed or authorised request
jobid as string ' id of this job for tracking
requestdate as date ' date job requested
status as string ' waiting, in progress, complete, attention required
shortdescrip as string ' short note on job
jobteststring as string ' contains job codes to be run
jobstartdate as date
jobenddate as date
TESTED
=========
completed jobs record
jobnumber as string ' id the requested job
technician as string ' who is running job
date as date
testname1 as string
upperres as string
lowerres as string
actualres as string
comment as string
USERS
==========
system users
userid as string
password as string
username as string
System works fine in access as is but ofcourse the tested table just gets bigger and bigger; as the results may need to be kept for 5-6 years I think I really should export data at some point!
So do you think just generate reports and maybe export data for jobs between certain dates every 6 month or so or dump results straight into dedicated table?
Thanks.
NeoPa 32,566
Recognized Expert Moderator MVP
The answer is an unequivocal "No" Malcolm.
Moving data to a separate database may be called for if space is tight (although even that is far less often than many seem to think), but I've not heard of any scenario where a separate table, or set of tables, makes good sense. If you were to read the linked article I suspect that you would come to that same conclusion yourself.
By the way, redoing your tables now, if they do not conform to the basics of normalisation, will be worth the effort. It may be some effort, but I can almost guarantee that the structure will trip you up at some point if you don't take this opportunity now.
nico5038 3,080
Recognized Expert Specialist
Basically you "create" a separate Job -> test table when filtering the JOINed tables for one specific JobID.
The database will create such a resultset rather fast when the JobID is indexed.
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: komal |
last post by:
hi all
basically my problem is i have to write a function such that when ever
i call this function in some other function .it should give me tha data
type and value of calling function parameter.and no of parameter is
calling function can be anything.
for example.suppose my function is function2.
then when i call
function1(int i ,char j,float d)
{
function2()
|
by: hubert.trzewik |
last post by:
Hello,
Is it possible to EXEC stored procedure from a query?
I want to execute stored procedure for every line of SELECT result
table.
I guess it's possible with cursors, but maybe it's possible to make it
easier.
Give an example, please.
|
by: laj |
last post by:
I'm new .
I have a table with student names and than several fields with classes
and all have 'yes/no'. Want to query the table to give total for
'yes's in a field. ie how many students (with primary id key) are
taking a class. eg. if 2 students out of 10 are taking one class then
the cell shows '2'.
another table has months for fields and class room , day, time for
records. Want the cell in month to reflect this total.
|
by: AFN |
last post by:
Hi. I'm pretty comfortable binding a single recordset result to a
datagrid. But now I have a more unique problem. I have 2 really long
stored procedures that cannot be combined at the SQL Server level (please
don't ask why, but it can't, and even if I could, it would take 10x longer
because they are very involved queries). But I need to mix the recordset
results into one HTML table, and need advice about the best way to do that....
|
by: RookieDan |
last post by:
Greetings fellow Accessers!
Im new but in Access, but I have some background in different coding.
I have a programme loading customer data into Access belonging to BMW dealers in Europe.
Every dealer reports several customers and I have today a query that sorts out how many customer data each BMW dealer sends in to us. The query is also referring to a startdate and enddate (to be filled in in a messagebox) so that i can choose time...
| |
by: kabradley |
last post by:
Hello,
Thanks for looking at my post and hopefully having an answer or at least a suggestion to my problem. I currently work at a financial planning office that deals with many clients and accounts. Each client may have multiple accounts such as an individual, IRA, and possibly a joint tenant account. Each one of these accounts for the particular client is contained in a 'Portfolio'. For example, Joe Smith may have 3 accounts: Joe Smith...
|
by: prileep |
last post by:
I have two tables Users and UserLogin. Here i will use two methods of table design. and which query will return me the result more fast.
The table size will be large that it may contain records in lakhs.
Method 1:
Tables:
Users ( UserID varchar(20) primary key, PassWord varchar(20))
UsersLogin(UserID varchar(20),LoginDate DateTime)
Query:
Select Users.UserID,UsersLogin.LoginDate
from Users U inner join UsersLogin UL
|
by: Matsam |
last post by:
Hi,
I am using ADO record set and want to display a string field in one of the columns of the table in the result page. But it is displaying unwanted line breaks, even if that column has necessary width.
For eg.
JOHN K. SMITH
is displayed as
|
by: nikolasapl |
last post by:
Is there a command so that instead of displaying the result table (for example the result of the following query) in the grid tab in the SQL Server Analyzer environment, to export it into an .xls file (excel file)?
select *
from table1
Many thanks.
|
by: Davy |
last post by:
The actual expression is '=(Count())+(Count())+(Count())+(Count())+((Sum()))'
When Quiz value is null/zero, the total is not generated
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |