Service Record Database Design Approach
Dec 15, 2005
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.
View Replies
ADVERTISEMENT
Aug 5, 2013
I run a service related company and I am trying to develop a service database that will automatically calculate the next service due based on the previous service date, the catch is that the service need to happen on the same week day. i.e. if the last service was on Friday 02-08-2013 and it is a monthly service, the next service is due on Friday 06-09-2013. Is there any way that this can be done? PS there are various service periods such as bi-weekly, weekly, 2 weekly, monthly, 2 monthly etc.
View 2 Replies
View Related
Nov 28, 2006
Say I have a piece of lumber in inventory that's 20 ft. long. I cut it into 2 pieces, one 13 ft. and the other 7 ft. Now I need to remove the 20 ft piece from inventory and replace it with the 2 pieces I just cut. Is there any way to automate this in Access? I'm have trouble visualizing and approach to this problem.
TIA
View 2 Replies
View Related
Feb 27, 2006
I've been tasked with modifying an Access97 query. Currently the query has a single contraint on the field titled jobtitle. We are interested in maintaining this constraint but in the event of null values defaulting a new contstraint to field called contacttype. Thus if there are no results returned for positiontitle = 'dtbc' return all rows where contacttype = 'prim'. It would seem an if then is the only option and I'm not sure how to approach this in access.
Thank you,
Josh
:confused:
Currently here is the query with the constraint only on contacttype = 'prim'
SELECT [Edition Header File].edition, [Edition Header File].instant, [Edition Header File].[page_#], [Edition Header File].franchise_page, [Edition Header File].subedition, [Edition Header File].subedition_page, [Edition Header File].supplier_name, [Edition Header File].supplier_line, [Edition Header File].sup_page, [Edition Lines File].product, [Edition Lines File].sup_style, [Edition Lines File].imp_style, [Edition Lines File].discount_code, [Edition Lines File].sup_disc_code, dbo_DistSup.Company, dbo_DistSup.LineName, dbo_DistSup.AD1, dbo_DistSup.AD2, RTrim$([CITY])+", "+RTrim$([ST])+" "+RTrim$([ZIP])+IIf(([St]) In ('LB','NT','AB','YK','PQ','BC','MB','NF','NB','PE' ,'ON','NS','SK'),"CANADA",IIf(Len(RTrim([St]))<3," USA")) AS city_to_zip, Trim([emailaddr]) AS tEmail, Trim([webaddr]) AS tWeb, [FName] & " " & [LName] AS contact, dbo_DistSup.Phone, dbo_DistSup.TollFreePhone, dbo_DistSup.Fax, dbo_DistSup.TollFreeFax, dbo_Alternate_ID_1.IDNumber AS asi, dbo_Person.ContactType, dbo_Person.ContactType
FROM ((dbo_Person INNER JOIN dbo_Jobs ON (dbo_Jobs.PerNbr = dbo_Person.PerNbr) AND (dbo_Person.EntNbr = dbo_Jobs.EntNbr) AND (dbo_Jobs.OrgNbr = dbo_Person.OrgNbr) AND (dbo_Person.USTOID = dbo_Jobs.USTOID)) INNER JOIN (((([Edition Header File] LEFT JOIN [Edition Lines File] ON ([Edition Header File].[page_#] = [Edition Lines File].page_no) AND ([Edition Header File].instant = [Edition Lines File].instant) AND ([Edition Header File].edition = [Edition Lines File].edition)) LEFT JOIN dbo_DistSup ON [Edition Header File].instant = dbo_DistSup.Instant) INNER JOIN dbo_Alternate_ID ON [Edition Header File].instant = dbo_Alternate_ID.IDNumber) INNER JOIN dbo_Alternate_ID AS dbo_Alternate_ID_1 ON (dbo_Alternate_ID.EntNbr = dbo_Alternate_ID_1.EntNbr) AND (dbo_Alternate_ID.OrgNbr = dbo_Alternate_ID_1.OrgNbr)) ON (dbo_Person.USTOID = dbo_Alternate_ID.USTOID) AND (dbo_Alternate_ID.EntNbr = dbo_Person.EntNbr) AND (dbo_Person.OrgNbr = dbo_Alternate_ID.OrgNbr)) INNER JOIN dbo_PositionTitle ON dbo_Jobs.PositionTitle = dbo_PositionTitle.PositionTitle
WHERE (((dbo_Alternate_ID.IDType)="inst") AND ((dbo_Alternate_ID_1.IDType)="asi"))
GROUP BY [Edition Header File].edition, [Edition Header File].instant, [Edition Header File].[page_#], [Edition Header File].franchise_page, [Edition Header File].subedition, [Edition Header File].subedition_page, [Edition Header File].supplier_name, [Edition Header File].supplier_line, [Edition Header File].sup_page, [Edition Lines File].product, [Edition Lines File].sup_style, [Edition Lines File].imp_style, [Edition Lines File].discount_code, [Edition Lines File].sup_disc_code, dbo_DistSup.Company, dbo_DistSup.LineName, dbo_DistSup.AD1, dbo_DistSup.AD2, RTrim$([CITY])+", "+RTrim$([ST])+" "+RTrim$([ZIP])+IIf(([St]) In ('LB','NT','AB','YK','PQ','BC','MB','NF','NB','PE' ,'ON','NS','SK'),"CANADA",IIf(Len(RTrim([St]))<3," USA")), Trim([emailaddr]), Trim([webaddr]), [FName] & " " & [LName], dbo_DistSup.Phone, dbo_DistSup.TollFreePhone, dbo_DistSup.Fax, dbo_DistSup.TollFreeFax, dbo_Alternate_ID_1.IDNumber, dbo_Person.ContactType, dbo_Person.ContactType, dbo_Alternate_ID.USTOID
HAVING ((([Edition Header File].edition)=[forms]![data entry header file].[form]![edition]) AND (([Edition Header File].instant)=[forms]![data entry header file].[form]![instant]) AND (([Edition Header File].[page_#])=[forms]![data entry header file].[form]![page_#]) AND ((dbo_Person.ContactType)="prim") AND ((dbo_Alternate_ID.USTOID)=1))
ORDER BY [Edition Header File].edition, [Edition Header File].instant, [Edition Header File].[page_#]
WITH OWNERACCESS OPTION;
View 2 Replies
View Related
Jul 30, 2007
Hi, ive been asked to provide a solution, for an electronic spreadsheet be sent out via email then returned by customers, once filled in for all the data to be collected onto one sheet that looks like the attached sheet. the easiset way i can see is to not use a spreadsheet but to use a datbase instead and just put it in the desired format, how easy is it to import mutliple spreasheets into correct fields on a dbtable thanks for any input or ideas
Al
View 3 Replies
View Related
Oct 15, 2004
hi..
I have tried to do a search on this but i cant seem to find something similiar. And I did a post of what i wanted to do here:
Click Me (http://www.access-programmers.co.uk/forums/showthread.php?t=74845)
This is my table structure:
http://www.geocities.com/gerald20000/Alpha/table.jpg
** I have keep this structure the simple. In actual of what i want to do, its more section under the 3rd lvl. as in more section under tblNAMe and tblRELATIONSHIP.
-> My structure:
tblQNA
CategoryNAME
CategoryMOOD
CategoryRELATIONSHIP
tblNAME
CategoryMALE
CategoryFEMALE
tblRELATIONSHIP
CategoryNEAR
CategoryFAR
tblMALE (Question related to Name - Male will be here)
MaleQuestion
MaleAnswer
tblFEMALE (Question related to Name - Female will be here)
FemaleQuestion
FemaleAnswer
tblMOOD (Question related to Mood will be here)
MoodQuestion
MoodAnswer
tblNEAR (Question related to Near Relation will be here)
NearQuestion
NearAnswer
tblFAR (Question related to Far Relation will be here)
FarQuestion
FarAnswer
So what am i trying to do? People would put in a question and an answer into a box. After that, the person can choose which category does the inputed question belongs to which category(male? female? .. ).This is actually a FAQ search engine.
So ppl will have an option to search for keywords and match questions. There should also be an option weather it should search from tblQNA or tblName or tblGOOD. So it has different level of searching. This is why it has such tree. So after searching, it will display the possible matched question (display question only). then the user can click which question to view the answer (together with the question).
Hope to get some advise. Is this how the best way to implement? OR is there a better method? pls advise and thanks in advance. ill be trying to do the access now. ill post as i goes along.
cheers
View 10 Replies
View Related
Feb 1, 2005
Is it possible to convert a database set up in Lotus Approach to an Access Database. If so, how can I do it?
Any help would be greatly appreciated.
View 2 Replies
View Related
Jan 22, 2013
Is there an easy way to import a Lotus Approach DB back end and front end to Access 2010?
View 5 Replies
View Related
Feb 26, 2008
Edit:I just realised i had accidently writted the title as (to do with importing access data) it should read (to do with importing excel data)This is going to be a trick hard to understand question but I will try my best to explain itI have a database set out in the following wayhttp://img524.imageshack.us/img524/1350/databasetableli1.pngThe way it works is; Let's pretend Access Programmers is a company and working on different forums is a different jobSo on one record it would readJames.90| Access Programmers|Tables Forum| Wed=3= Mon=2Then the record below might readJames.90| Access programmers | Forms Forum| mon=5 tue=6So each record is one unique company,Project and CTR which the person has worked for that week meaning if you only work on one forum you would only write one record out each weekNow the data i am receiving is in an excel file where it's set out in a daily basis Where One Day Date|Name|Company|CTR|etcSo if a person works 5 days a week on 2 companies each day that is 10 records when it should only be 2 recordsSo to sum it up. My database is set out weekly and the excel data is set out dailyMy questionWhat would be the best way to convert this data into the database. Changing the database structure around is not an option and i can't change the format we recieve the excel data in. I can change it once i have the file thorough a converter but i can't change the raw source of the dataWhat would be a way to solve this problem because i am completly stummted and am open to any option of converting or anythingThankyou for your time. Also if you have trouble understanding what i mean Please say so and i will upload a copy of the database and a copy of the excel sheet!
View 10 Replies
View Related
Aug 18, 2005
Hi,
I'm trying to make a database where a Date and a Service Number is entered into a form.
I have 2 buttons, one to automatically enter the Current Date and the second to automatically enter the Service Number.
The service number is incrimented by 1 each time and reset for every day.
eg.
Date Service Number
January 1, 2005 1
January 1, 2005 2
January 1, 2005 3
January 2, 2005 1
January 2, 2005 2
January 5, 2005 1
January 5, 2005 2
I already have the button that enters the Current Date. However, I am having trouble with the second button.
How can I code the button so that it searches the table for the Current Date and finds the record with the greatest service number for that date? After this is done, I want to create code that will automatically enter the next service number into the Service Number Textbox on the form.
eg. Using the Example Table Above
The date January 5, 2005 is entered into the Date Text Box and the Search Next Service Number button is pressed. The number '3' should show up in the Service Number Textbox
Sorry if its hard to understand, but thanks anyways.
I just realized that I placed this in the wrong forum, Sorry for the inconvenience.
View 2 Replies
View Related
Feb 28, 2008
A child in our company can have up to 4 different service providers offering different services at a time. I have to do a report for each individual service provider and there delivered services. The problem is, all the providers and delivered services are listed on one record (the child's record). How do I make a query that will only pull up their delivered services in a record and not the services of the others attached to it? Should I use IIF? If so, what would be a good statement to use? If anyone can help me out, I'd be thankful. Let me know if additional information is needed.
View 1 Replies
View Related
Oct 30, 2005
Hi All,
When I open a query in design view it can take anything upto 20 seconds to open. Tables are also slow to show, with a marked "slowness" over all. I tried the same database on an old laptop & the tasks were almost instant.
My main machine links to a file server, but the database files are not server based.
After much troubleshooting I have dicovered that it is the XP Workstation Service that is dragging it down. If I stop the service, then Access is rapid!
I have read all I can find on the subject, but nothing that helps. With all the parameters in the Workstation Service in the registry, is there a setting that I can change to speed things up?
System details:
XP Home (SP2)
Access 2002 (SP3)
Many thanks!
Dave
View 4 Replies
View Related
Nov 8, 2005
Hello everyone. Im relatively new to access, i've only been using it for a few weeks. There are certain aspects that I cannot get my head around.
Is anyone willing to let me email them what I have done so far and the criteria for the database I am making and advise me where I am going wrong and where I need to be heading to get it right?
Thanks very much. Any help will be very much appreciated.
View 14 Replies
View Related
Apr 15, 2007
When creating a database is it true that ideally i should avoid using the lookup wizard at table level and instead do that with combo boxes at form level ?
Thanks
View 6 Replies
View Related
Feb 13, 2008
Hi
I have three tables
1 tblAssistFMWork in which is stored the fields JobNumber, SiteRefNumber, SurveyorNo
2 tbltable1 which has the field SiteRefNumber, Address1, Address2 etc
3 tblAssistFMSubJobNumbers which has the fields JobNumber, SubJobNumber.
JobNumber is the relationship between tables 2 and 3 and SiteRefNumber is the relationship between tables 1 and 2. There are other fields in each table but these are the relevant ones for this question.
Each JobNumber will have a minimum of one SubJobNumber but may have many. Each SubjobNumber starts at 1 for each new job so this may look like this in the table
JobNumber SubjobNumber
0001 1
0002 1
0002 2
etc
Each SiteRefNumber may have one or many JobNumbers
I wish to enter the date a SubJobNumber is completed and have been struggling with pulling the record into a form using a combo box because of the need to enter the JobNumber and also the SubJobNumber. So I decided to create a new table tblAssistFMJobDateCompleted which has the fields
JobNumber, SubJobNumber, DateJobCompleted
I need to report on a jobs status ie done or not done, but when I created the query for this it would only pull the records which have a completed date on them.
Could someone please give me some advice as to a way forward.
Thanks
Richard
View 3 Replies
View Related
Feb 20, 2005
I have been reading other posts, and this where i should have started from the beginning,I am not very experienced with access (or forums as yous propably know) but can learn anything in no time (hopefully).
I would like to design a database to store all the info about a drag racing event including;
• event info (Event name, track name, event date)
• car info (Car name, gearbox type, diff ratio, engine name,)
• driver info (driver firstname, driver lastname)
• Engine info (make, model, engine compression, carburetor, front carburetor jets, rear carburetor jets, fuel pressure, engine timing at idle, total engine timing)
• Race info (track temperature, relative altitude, milibars, humidity, air temperature, tyre type, tyre size, tyre pressure hot, tyre pressure cold, stage RPM, shift RPM, reaction, 60foot time, 330foot time, 660foot time, 660foot MPH, 1000foot time, elapsed time, elapsed MPH, comments.)
My issue is design of tables and relationships. as you can probably see, obviously cars will do many runs at each event, and there engine setups and car setups(diff ratio) can differ each run. there might also be different drivers for the same car. weather info like track temp etc also change from race to race so I think it is possible I might need some sort of race time as-well. My main reason for the database, apart from organizing all my results would be so that I could do a run at an event and search previous races by the current runs weather info (relative altitude, milibars, humidity, etc) as to set the car up similar to keep it’s elapsed times consistent. Please, please, please help as I am at a total loss.
Thanks in advance!!
View 14 Replies
View Related
Aug 5, 2006
Hi all
I've created a database from scratch. No data attached anywhere yet - I'm planning to input the data manually through my Form.
Well I have about 15 tables - CustomerInfo_tbl is the main table with the field RefNumber my primary key (it's an Autonumber which is sequentual). The rest of the tables are normal tables which I plan to link.(these tables also have RefNumber fields which are LongInteger) All the tables have about 10 fields in them.
So then I have 2 choices - I can make one big table (about 150 fields) or 15 tables with 10 fields each and the join them. I heard somewhere that it's better to have small tables and then link them up. So I'm going the smaller tables route.
I'll tell you what I did. I joined all the tables to my CustomerInfo_tbl with a one-to-one join. Then I made a Query, and then I set the RefNumber of the other tables equal to the RefNumber of my CustomerInfo_tbl.
I have 1 problem. When I enter some data into the form (I do not enter all the fields I leave some blank) it isn't displayed in the query, but it is displayed in the appropriate table. Only when I enter all the fields it is displayed in the query correctly and also in the tables.
Any help? I hope I made my question clear enough.
Thank you in advance.
Cheers
kruger101
View 2 Replies
View Related
Nov 7, 2007
Hi, i got back to working on databases again for the first time in years and ive got confused on the offset.I went to set about desigining my tables and cant figure out how to do it.most of its simple stuff however this one bit stumps me.
Im designing a database to hold stock information in work we manufacture parts for rally cars and when a customer rings up i need to have the price at hand.I can store the prices etc of individual parts,however some people request kits which would have there own stock number and all the same relevant data i.e. Item Name,Type,Cost,Number in stock,Selling Price.......
These kits would consist of several items from the stock table.and when a kit is sold it will be inserted into the Sold Items Table together with the other individual parts a Customer may order.
Any help would be gratefully recieved.
View 11 Replies
View Related
Oct 22, 2004
Hi all
I have a new project but before I get started, I need some advice on design.
I'm designing a student report card which I'll implement for 1300 students next year. The concept is an ongoing electronic markbook where teachers update the progress of each student and then at the end of the year print a report based on the captured data. The data is then passed on to the next teacher the following year.
My problem is data storage. During the course of a year a teacher may mark off up to 550 things a student can do. After 7 years of schooling (7 x 550) this amounts to a large amount of data. The thing is, I could reduce the number of fields if the multiselect list box stored multiple pieces of data. Option groups are no good because only one piece of data is stored and I certainly don't want thousands of check boxes.
Basically, I'm looking for a way to store multiple pieces of data in the one field. I'm also looking for a way in reducing the amount of clicks (i.e. one click instead of 2 to enter data)
I can't delete data because it's an audit requirement to keep it.
Any ideas??
I appreciate any help
View 4 Replies
View Related
Mar 4, 2005
Can someone help me out with the design of my database? I currently have it set up one way and I am running into problems when I need to update information?
here are the attributes I have to put in the database for Printer cartridges:
ProductName
Manufacturer
Category
ProductCode
Price
Weight
Description
--------------
There can be numerous products with the same name
(for example: C120 is a product name but can be made by HP and by Canon)
There can be numerous products with the same ProductCode
(for example: F416301700 is a product code but can have multiple products with that code)
Currently I have it set up like this:
3 Tables
--------
PRODUCTS
ID
PRODUCTNAME
MANUFACTURER
CATEGORY
DETAILS
ID
PRODUCTCODE
PRICE
WEIGHT
DESCRIPTION
COMBINE
ID
DETAILS$ID
PRODUCTS$ID
-----------------
I have created that combine table to link the two other tables together.
I am running into problems now when i want to update a product and I don't know where to take this from here. I think if I have a better design from the get go it will prevent problems in the long run.
Can anyone help me with this?
Thanks
dp3133
View 7 Replies
View Related
Mar 17, 2005
Hi, I’m fairly new to access and I’m creating (well, want to create) an employee database. I was wondering if anybody has created an employee database that keeps track of employees information like; personal information, salary, training the employees have taken, performance reviews and much more and if so do you have any suggestions on the design. Pretty much this database will be an electronic version of the employees personnel file.
Or does anybody know of any sample databases, not necessary an employee database that have been shared here in the past that I could look at and get ideas (formulas, qrys, tbls. etc.) from there.
Thank you.
View 2 Replies
View Related
Jan 11, 2006
Hi,
I am designing a db for my car event that happens once a year.
It needs to hold car info like engine specs, car specs. Entrant details and event details, merchandise info.
I started out designing on paper (Can’t stress how much this helped a definite must for all noobs and experienced I would imagine) and went from there.
Ended up using northwind and modified it to suit my situation.definately recommend this to beginners as it gives you a good look at databases and great starting point. anyway this db has everything I need I think, the db is for holding info about participants in a car event. There is a form that the entrant would fill out asking there details and the car details. There is also a section on the form to buy merchandise as the car event has its own T-Shirt and DVD.I have added this into the database but I am having trouble finding where to put the T-Shirt Sizes.
E.g. there has been 3 events so far (1 per year), car event 1, car event 2, car event 3.each event has had a T-shirt. this t-shirt comes in sizes, xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl.my problem is do you add the same t-shirt in the products table 10 times just with different sizes.
also the Entrants form has subform event, where you select the event each year as to not have to enter entrants twice.i can not seem to figure this out either.
Once you look at the db you will have a better idea.
The database only has a very standard form for getting around while I complete the design, all the beautification will come after this stage.
Any other ideas would be greatly appreciated.
Thanks
(PLEASE COPY AND PASTE THE LINK INTO YOUR BROWSER OR IT WILL NOT WORK DUE TO REMOTE LOADING)
Access 2000
Access 2002-2003
View 1 Replies
View Related
Feb 12, 2007
Hello,
I'm totally new to this forum. I have a database with the vehicle information in one table. The Vehicles' Odometer reading stored weekly in another table. I have the vehicles' service history with the Odometer reading of the last service in another table. The first two tables are linked by parent, child relationship. I now want to forecast the approximate vehicle service due date based on this information. All I want to do is extract the last 10 Odometer Readings from the Weekly Odometer Readings Table. Find the Average Kilometers with those 10 values. Come up with a figure. Determine the last value entered in the Weekly Odometer reading along with the date. Pull the last service history for the vehicle, with the odometer reading and date. Compare these 2 readings to see if the vehicle is due for service, and if so based on the average Kilometers run every week, determine the approximate date the vehicle is service for due. I badly need help on this one. So if someone could help me on this please let me know.
Thanks
View 3 Replies
View Related
May 7, 2013
Simply put, I have a front end that does a bunch of file moving and if it encounters an error and stops processing (which is intentional) I am the only that can login to the machine and fix it.
Is there any way to run it that would allow others to login on their own user profile, solution the problem, and start the processing again or am I stuck to a "per profile" instance?
I can't just let others login as me
View 2 Replies
View Related
Sep 11, 2005
I am combining 12 Databases. I have split all 12 between the server and the desktop. My question is “Should I combing all of the data table on the server into one database or should I leave all of the individual application data tables in separate Databases on the server”. Additionally, is there a limit to the number of tables an Access DB can handle? My inclination is to keep the functionality separated but the problem I have is that some of the functionality within the applications overlap. Recommendations!
View 2 Replies
View Related
Sep 23, 2005
Hi Guys,
I have sort of problem - I'm trying to desing the database for one of the organisations.
The entities are: Family, Child, Referral, Voucher, Receipt, Provider, Activity.
That's ok so far. I've been asked to do the option for the new address of the Child - in case if they move to the new place and keep the old one as well.
Family table is the 'child table type' for the Child table - family can have more than one child, but one child can be part of only one family (one to many relationship).
I thought to make a seperate table 'tblAddress' and store all addresses in this table - then link them to the family - this however requires from me to have additional field for - for instance - second address, third address, which in case there was no change in address would remain empty value.
The other option - which obviously is absolutely waste of space is to add straight away additional address fields in the 'Family' table itself.
Don't you know of any other, more appropriate solution?
If you need more detail - I can send the copy of the database which I have created so far.
I would appreciate any help.
Thanks,
Scabro
View 14 Replies
View Related