Tables :: Database Design Structure For Controllers And Heating
Mar 4, 2014
I haven't been on Access for a while so im still in the process of getting the cogs turning....... i am trying to design a database structure for controllers and heating.
each controller can have many heating units
each heating unit can use many controllers
rather than code everything, how could i set this up?
Example:
Controller01 can be used with Boiler01, Boiler04, Boiler12, Boiler 20
Controller02 can be used with Boiler01, Boiler05, Boiler15
Controller03 can be used with Boiler02, Boiler03, Boiler04, Boiler20
I have a table with a ControllerID and a table with a BoilerID. In the boiler table, i have the ControllerID and a one to many relationship.
Booking - BookingID Course - CourseID Company - CompanyID Materials Materials_on_course (had to have this in order to avoid many-to-many.) Employee - EmployeeID Employee_runs_course (had to have this in order to avoid many-to-many.)
I'm stuck with the Course and employee thing. Each employee has an expertise or two, and therefore each course has an expertise needed to run it (a specialist if you like).. so the PK in 'course table' is Course ID, you cannot have two PKs in one, so would it be right to say that the 'expertiseID' would have to go in 'Employee_runs_course' ?.. what would i do then? place 'expertise ID' as an FK in both employee and course tables?
Extra info:
Clients (tblCompany) can book one or many courses A course can have one or many bookings made for it A booking has one or many employees/courses An employee can work on many bookings (obv if available) Each employee has one or two expertise (usually only one) and therefore a course has on expertise attached to it.
__
If you have a better way of designing it, shoot! Attachment: ERD (kinda) of first draft.
I am developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.
With this table structure there is a many-to-many relationship between Claims and Events, but, there will only be one of each event per claim. Is there a better way to set up the tables to enforce a 1-to-1 relationship?
I was planning on creating a form for the Claim table with a subform for the Events joined on ClaimID and fkClaimID, but that would allow users to create more than one set of Events per claim, if that makes sense.
Hello all, I am starting to get an idea of how access works (at least I know now where the surface is). I am having a design/table/form problem. Here is what I have: a form called 'Enter Product' , in this form I have a YearDate, YearMonth,YearDay fields (I am not sure how queries work at this point of time, and if searching through dates is reliable, so these filelds might just be for the moment and replaced later on with a date field) these three text boxes are bound to the Readouttbl. I have a combo field MachineID also Bound to the readouttbl and gets it's values from the MachinListClients_table. I have a combo field ProductID also Bound to the readout table and gets it'd values from the ProductList_table and a field readoutAmount bound to the Readouttbl
what I want to do: is when the user opens this form they are able to add the amount of cups (readoutAmount) for each Product a machine has at that readout date
Now the only way I see that possible is to add many extra colums in the Readouttbl (product1 , readout1, product2, readout2, product3, readout3, ....) Is this the right way to structure this? or is there a better way? I am attaching my database file so it is easier to understand the structure that I have (which might be absolutly wrong, please comment if you see anything that you think should be changed in any part of the database)
I am still new at database design, and cant quite come to terms with my project and access way of doing things.
I have to keep a register of people who participate in projects. The projects can be of two different kinds. BUT (here comes the tricky part) The projects are being evaluated on three different indicators, with each one of these having 4 measurements, in the range of 4-0. That was a quick introduction. Now let me break it down in parts.
The people:
I have made a Uniqe identifier (Social Security number (PK))
First Name Last Name Department (This can be 4 different departsment) made a drop-down menu type.
The Projects:
Unique identifier (Project ID (PK)) Social Security number Project Type Start date End date
Project type:
Unique identifier (TypeID (PK) Project type (Cti / Regular)
How might i design this the best way, so i can combine the people with the projects there on. And which type.
There can only be one person, but he can be on many projects. These projects can vary in type. My problem is ensuring there connected proberly.
Furthermore, once the basic design is made i need to make evaluations based on their performance if they are on the projecttype "Cti".
here i need 3 x this:
Evaluations:
Objective (range 0-4) Baseline reading (range 0-4) Midway reading (range 0-4) End reading (0-4) Success = Yes/NO (here i will do a End reading <= Objective formula).
That was a rather long list, but i have sat working on this in three whole days, and im getting a little fed up with not knowing up-and-down.
I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck.
Is there any way to keep track of a database structure in access? For instance, which query relates to which report? Sometimes I create queries that are no longer needed but if there are a lot (which there are!) it can be easy to delete one that's needed.
I created a database to track tardies and absences of my employees. I would like to be able to sum the number of absences and tardies for each person per month and graph it.
Per our attendance policy, 6 tardies = 1 absence. I need to take the total number of tardies that month/6 and add to the total absences that month to equal the total attendance for the month.
I need help. Can you please view my database and give me some ideas?
I am designing an application that tracks information on Choir membership and sheet music that we have on file. I am starting with a database of church members. There are four different choirs and choirs share some members. Some members of some of the choirs are also not members of our church so I will have to place non members in the member table. I am new to database design and would like the collective wisdom of this list to tell me of any problems I may encounter before I start doing any detail work.
What is the best way to deal with someone who is a member of more than one choir and may belong to a different section in this other choir (Tenor in one and Bass in another). An individual may also hold different offices in various choirs.
The table structure I have is as follows
CHURCH MEMBERSHIP DB: MemberId Autonumber (pk) FirstName, Text MiddleName, Text LastName, Text DateJoined, Date Phone, Text Address, Text City , Text Zip, Text EmailAddress BirthDate, Date Member, Boolean
CHOIR MEMBERSHIP DB (How do I efficiently track someone in > 1 choirs) MemberId, FK ChoirId, FK FolderNo RobeNo Section ChoirOfficeId, Fk
CHOIRS DB (This lists the various choirs in the Church) ChoirId, pk ChoirName, Text DirectorId, FK (Pointing to Member DB, Person may not be member of any Choir)
MUSIC DB CatalogId, PK Title Composer Arranger Type (Single Copy/octavo or book/collection) PublisherId, FK PublisherNumber VoicingId, FK (From table with possible voicing) NumCopies UsageId, FK (Where in the service is it appropriate Location, Text (Where in the filing system, or off site) ClassificationId, FK (List of classification/genre in table so can update)
PUBLISHER DB PublisherId, PK PublisherName PublisherAddress PublisherPhone PublisherWeb PublisherContact
MUSIC CLASSIFICATION DB ClassificationId, PK Classification, Text (Christmas, Easter, general anthem etc)
To be able to track performances and plan services and performances I have the following table.
PERFORMANCE DB (This is to keep track of and plan the regular service) PerformanceId, PK Pdate, date (Date of Past/Planned performance. Possibly more than one per day) ServiceTypeId, FK (From table of types of performances – morning service, evening, etc) Location Speaker Pianist Organist Introit Invocation Anthem Meditation Benediction (etc)
I would also like to be able to prepare mailing labels for the various choirs as well as the general membership from this DB. My primary focus will be on the music. I would like to have an efficient music DB that I may find out what music I do have and when I last performed them, what options for performance (usage and classification)
I am trying to figure out how to store data into a database to be use later in a web app. So I have a shirt and it can come in four different colors. What would be the best way to enter this into the database. I'll have shirts, jackets and accessories which I have give each their own table. The shirt one is giving me trouble though, I just don't know how to organize it.
I'm doing some changes to a database in work, but the person who created it doesnt work there anymore and so nobody knows anything about it. The database has been split into a front and back end, which is fine, but there appears to be 3 seperate databases linked to the main one, each with a .mde file for security. Depending on which department you're in, you access your database. Any information being added is updated in the main front end database. Does anybody know how that will be linked? I'm just probably going to have problems when encorporating my changes.
Hello, I am looking for someone's professional opinion to help me clear up some unresolved technical issues in my mind... =P Ok, I have a contact management database that would seem really straightforward to me except for the fact that I am dealing with 2 primary entities. In this I mean I have an Individual table and Organization table. These two tables have primarily the same field data, such as both having address data, phone (contact info) data. There are some unique fields to one entity that aren't contained in the other entity though, and visa versa. One important issue is that 0, 1, or more individuals can be related to an Organization. Therefore I now have linking tables for IndividualOrganization, as well as linking tables for Phone and Address data. My primary complication is that since both Individuals and Organizations have address and phone data, the respective linking tables for them contain: an addressID/phoneID key, for the appropriate linking table, with an OrganizationID key and an IndividualID key in each table. So, the way I am thinking is that if the record pertains to an Individual, the appropriate ID will be in IndividualID and the OrganizationID will just be 0, or empty. This brings up issues with primary keys being blank. I am wondering if there is a better way to do this that I am overlooking or if I am in fact on the right track? I have contemplated combining the Organization and Individual tables into one but that really doesn't seem like the best solution. If anybody has any ideas then I would GREATLY appreciate it. I had a working database the other day but now I am separating the phone and address info out of the primary tables and have really got a mess on my hands. =P If anybody would like to look at my database structure I'd be more than happy to post the back_end and the front_end for you to give me your honest opinion about any trouble areas. Thanks BIG TIME in advance to any nice souls willing to help out a struggling programmer who doesn't have any close support on this one as I am working alone, :rolleyes: Dana S.
I am building a database of in-car audio systems. Stored in this are simple things like vehicle information, audio brand and other general information. The main information I need to store includes details about speakers (position, quantity, material, range, size etc.) and amplifiers (power output, no. of channels etc.). Sounds simple I hope.
I have been thinking about this extensively and have come up with two solutions, each with its own pros and cons. My primary concern here is how the speaker and amplifier information is stored:
1) Tables specific to car regions (e.g. Rear Speakers, Front Speaker, Surround Speakers) have the fields 'Range', 'Material', Size, etc. Range and material can read their values from other look-up tables no probs. These would be linked to the main table via it's primary key in a 1-M relationship. -Pros: Ease of construction and general handling of data Flexibilty/expandability (in terms of speaker quantity) -Cons: When it comes to data entry the form would need to be continuous and continuous forms I have found to be a pain in terms of referring to individual records! I want some of these controls to conrol a diagram for my report (and input form) - such that they show/hide speaker pictures on a car diagram. Also, I have not yet figured out how to validate a continuous form in this situation or delete records from it either (should the user makes an error).
2) Again, tables specific to car regions but this time with a field for each speaker range (mid-range, tweeter, woofer, etc.) and it's associated data, tied to the main table via the main table ID in 1:M relationships. -Pros: Easy to manipulate form/report properties because every speaker has its own field Easier to remove erroneous data (I imagine) -Cons: More complex construction (and less efficient) - e.g. material occurs more than once for each record so to look up material form a central pool requires an intermediate table Many many more fields! Non-expandable in terms of speaker quantity
Similar principals can be applied to amplifiers so I haven't mentioned those.
I don’t understand how to structure tables and relationships.
I want to build a form that allows the user to search for a postal service from a group of carriers.
User must be able to input into a form:
Weight in g, Kg or lbs Insurance level required Whether signature is required (Yes/No from drop down list) Collection/drop-off options (tick boxes)
Each service has a different insurance level, some need signature; some services collect, and all have different rates.
I just want to know how to structure the database.
The main part of my question is what would be my main table be and what fields would be contained in it, and what foreign keys would be introduced to pull in info.
Would you have a different table for each service?
Access 2000: How can I populate a two field table (1. Table Name 2. Field Name) with the name of ever field within every table in my database using VBA code?
I would like to build a database to keep track of tardies and absences in my dept (~70 employees).
I need Date, Name, tardy or absent,
My current table:
TblEmployees EmployeeID PK Last Name First Name Team
TblEvent EventID PK Event (Tardy or absent)
TblAttendance Date EmployeeID EventID
Is this a good structure? I need to be able to run a query that will sum the total number of tardies and divide by 6. That number will then be added to the total # of tardies. The query needs to only show the values over the last 6 months. Any help is appreciated.
Lets say u have a database that keeps track of all items sold and their respective prices. So u have a items table that keeps the product information and price. Then these items are displayed on invoice kept in a invoice table linked with a foreign key and a one to many relationship.
My question is lets say the price of an item should change....will the invoice table of old invoices display the new price or old price. Is this where the referential integrity comes to play ? Should u select it when doing the relationship ticking the cascade update related fields and cascade deleted records boxes respectively or not ?
Hi everyone. This is my first post here, I hope I'm making it to the right forum!
I'm currently experimenting with databases with an idea to move them online in order to create SQL & PhP driven websites. I'm a long way off actually making them live, though, and have become stuck today on trying to create the best structure for a specific database.
I've searched for example databases to see how other people might have solved this problem, but it's very hard to find another database with exactly the same issue.
I hope someone can tell me the best way organise my data! I'm racking my brain trying to reduce data redundancy and make the most logical database I can. I'd appreciate any help, thanks!
Basically my current database involves cataloguing a particular video game series. Each game title in the series has information attached to it (eg. platform, developer, trivia, data released etc.), but some games have spin-off titles with the same information stored about them.
If someone is viewing a spin-off title I want them to be able to see that's it's connected to a "parent" title and vise versa (someone looking at a "parent" title can see all the spin-offs).
This database is as much an exercise in creating a completely "correct" database as anything, so I want to reduce data duplication as much as possible (completely?).
I've attached a picture of my database as it stands at the moment (with no spin-off information added), I hope this might help someone understand my problem.
Thanks for any help! It's much appreciated! Databases make my brain hurt sometimes, and it's hard to know then "best" way to do something!
- Johnny W
PS - Here's the "key" to the diagram.
tblTitles - Game information tblAlternates - The same game on different platforms (eg. PC, PlayStation, Nintendo Gameboy etc)
Actually, I think the rest is pretty self-explanatory! Thanks for any help! -J
We are currently using Access 2007 for all our database needs but so many different people have had access to changes things that now they are so convoluted that my boss is wanting me to build a brand new database... Is there a way to take all the tables in one database and migrate them over to the new database that I am building?
If i make minor changes in the backend db structure (eg add a feild, change properties of a feild in a table,). These changes are not reflected in the frontend. how can thses changes be reflected in the frontend usig the simpest method.
I am creating a database where the user can import data from a number of spreadsheets (one spreadsheet for each company). Currently I have one table for each company with the year being the primary key. Each year when a rolled over spreadsheet needs to be generated for a certain company I have Access import the data to a blank template in excel which will display the prior 4 years of data (for informational purposes) and leave a column blank for the end user to fill in the current year data. When returned the current year data is imported into the database.
My problem is that I cannot see a way to do this without having a seperate table for each company. If I have one table with all company data I cannot use the year as the primary key. Any suggestions on how I can reduce the amount of tables I need. Setting up the rest of the tables wont be a problem, but future users of the database will have to go through the process of creating tables for new companies and creating all the queries and macros associated with that table.
I have a table where in 286 different fields have to be updated by the users. ( Nature of project And i have 10 fields which are coming as a input from another table, so i use Append and Update query to add the new datas and update the datas in case of any changes in the input from another table. ( to avoid manual corrections)
Now coming to my real problem, all the 286 fields have to be updated by the user's are check box type, now what is the solution since access will not accept more than 255 fields?
If I want to duplicate just a table, I can easily select "structure only" under paste options. Is there an easy way to do the same thing for an entire database (tables, queries, etc.) all at once?
In short, someone has a database. They're willing to share the database itself, but not necessarily its contents. The database has a user interface, so the people who use the database don't necessarily know much about Access. Therefore, I can ask them to follow a few basic, built-in Access menu options, but can't ask them to do something manually, such as copying the entire file and then manually emptying the tables. This seems like something that should be easy, but I can't find it. Any help?
