Tables :: Redesign A Database To Include More Complex Relationships Between 3 Variables
Feb 21, 2015
I need to redesign a database to include more complex relationships between 3 variables non of which are mutually exclusive.These are my 3 tables/fields. Each field is unique in its table.
There is no clear relationship between the variables - ie they can all be 1 to 1 or 1 to many or do not have to exist at all. So I realise now (after 14 years of working with this data), that I need to have what I would think of as holding tables between the primary tables that hold combinations of the variables. Do I do this in one table that holds all 3 variables in non-repeating combinations (although this would need to allow nulls) or do I do it in 3 separate holding tables?
I would like to create a small HR database that holds:
* Employee details (Name, Date of birth, Entry Date, Exit Date, Function,Department, Shift) * Departments * Departments and capabilities needed * Employee, departments and capability achieved (1=yes, 0=no)
The idea would be to have a link between the employee the departments capabilities I can tick fields of the capabilities they have or have achieved.
When I have this I could run a report that shows a score per employee For example:
Name: Department: Orderpicking: Packing: Cutting:
John Warehouse 1 1 0
As the tasks / capabilities required are different per department you would see something different if you would have user Jane that works in accounting
Is there anyone that can give me some hints so that I can get any further??
For the moment I have 3 tables:
1. Employee information - John, Jane 2. Departments - Warehouse, Accounting 3. Department tasks - Department, Cap1, Cap2, Cap3, Cap4
Hi, I am new to this post. I am a physician with interest in database designing. I have been trying to design a database for my clinic for few months but am unable to make one. I have been searching/ reading alot of info and came across this thread. Maybe someone can help me. Actually, I want to make a database regarding ultrsound scan examinations of patients. I have five tables. 1. Patients. (patinetid*, patientname, age, sex, address, contact no) 2. Physicians. (physicianid*, physicianname, speciality, address, contact no) 3. Scans. (Scanid*, scanname, charges) 4. Scan orders.(scanorderid*, patientid*, physicianid, scanordernumber, scandiscount, totalcharges) 5. Scanorder details.(scanorderdetailid*, scanorderid, scanid, charges, discount)
I want to have primary key for scanordernumber which wil be the patient number and should this be placed in patient table?? All the ids have been linked with one to many reltionships. Actually I am unable to set proper relationship. So when the patient arrives he is registered with a unique number, a physian name with date added and scan ordered is entered. Sum calculated. I have done the later part with the form all designed but the relationships and primary key are all messed up.
I can post an image of relationships or blank database.
My db is split into front-end and back-end. In the f-e's Relationships schematic, I can see the relationships as they were defined at the time when the db was split, complete with the one-to-many symbology.
I can add a new table to the b-e and set its relationship as one-to-many, enforcing referential integrity and cascading as I wish - and the schematic (in the b-e) reflects that.
In the f-e, I can then use the <Get External Data - Access Database> function to link to the new table, and I can add the new table to the relationships diagram in the f-e. I can also drag and drop to link primary and foreign keys (within the f-e), but cannot select one-to-many. I'm OK with that, as I understand that the relationship is within the b-e, and this is just a diagrammatic representation.
But I can see the one-to-many relationships between the tables which existed when the db was split, and I would like to be able to see the new table's relationships in a consistent fashion. Updating the linked tables via the Linked Table Manager does not do the trick.
Surely I don't have to split the database again in order to achieve a consistent diagram - do I ?
I really need some help. I am supposed to have a rough working copy of the database ready by Friday and at this rate I am not going to have it done. I have an Access database now that we enter units in and track through our manufacturing plant to shipping. We are bringing an AS400 ERP system on line in two weeks that will replace this. The problem is we lose a lot of the tracking reports that we are accustomed to.
What I have been tasked to do is redesign our Access interface to work with the AS400. One of our programmer's has taken an AS400 report and exported it to a .csv file. I am using this file to update and insert into 2 Access tables. My problem is I need to automate this. I cannot get this to work. I have the code set under Frontier update on the menu that appears. My prepare for table query is no longer working. It was working in Access 2003 but I started getting an error on the main table update so I switched back to 2000 and now it not's working at all. I get an undefined function error with MID. Also, I am not sure of the best way to set this up. Users currently have an interface with the backend being on the server and all tables are linked. The way I am setting this up the users will be updating to a local table in their interface. Is this the best way? I need the update to occur every few minutes as the users will be updating in the AS400 and then Access will retrieve this new information from the .csv file be exported every few minutes. If I can do this on the backend that would be great, but I would need some kind of job scheduler right?
One of the other problems I am having is I need Access to create WOSD (Work Order Start Date) and WODD (Work Order Due Date). This is based on the DelDate, DrStyle and Prefin. For example, if the DrStyle is RP22, the the WOSD should be 6 days before the delivery skipping weekends. I am not sure how to get Access to update this when we do the update. If the deldate changes which is will (part of the reason for the update) then I need it to recalculate the WOSD and WODD as long as the Status in the Tracking table is In Contracting, In Layout, or Ready for Production anything past that point is already being built and the WOSD is not relative.
I would greatly appreciate any help I can get. I am still learning and will be taking some classes within the next month but I need to have this ready for testing ASAP.
I am using DoCmd.TransferDatabase to transfer 2 tables from one database to another. This works fine, however, the user permissions for those tables do not carry over with the table and I have to go into the database and set them as they were in the original database.
Simply, I want to allow complete access for all users to the tables in the second database.
I will try to explain this as best as possible, please let me know if there's any more info i can provide in order to get a better response. I am making a database that links the following tables:
Product Fund Company
The value for each of these combinations will be a simple yes/no, but the problem with the relationship design comes in because of the companies. For each company there are multiple smaller companies, for instance B company is comprised of a1, a2 and a3 entities. The information I am puting into the database is specific to the a1, a2 and a3 entities, not B company as a whole. What i need to query is B company as a whole. For instance, i need to know that B company can sell widgets(product) through 1, 2, 3 and 4 (funds).
I can't think of a way to consolidate the data from the smaller companies into the data for the company as a whole, especially since the smaller companies may sell the same products in the same funds. My current Table relationships looks like this: http://img338.imageshack.us/img338/5282/tableif8.jpg but i don't know how to manage the company as a whole(eg. B Company above)
The second problem i have is once i have the data for the company as a whole i need to be able to query multiple funds. I thought this was going to be easy at first by switching "or"s to "and"s but, even after playing with parenthesis any "and"s that i add just make it so it is looking for "1 AND 2 AND 3 AND 4" in one field. what im trying to make it do is query (Product = z, Fund = 1, available? = yes) AND (product = z, fund = 2, available? = yes) etc.. and have it output a list of Companies (eg. B Company)
I realize that I can query like this by querying a crosstab with funds as columns, but the problem with that is funds will change because I have 5 groups of people using the database who want different sets of funds (and it will output entities, not companies).The database needs to be dynamic so when one group of people changes their preference of funds or we add a new fund I dont have to make a brand new query/form/report.
Any comments/quidance is appreciated, please let me know if you need any more information. Thanks! :)
There is a mapping table called, lets say tblMappingTable which has the following fields:
Contract0001 - Contract0016 1 - 11 2 - 12 3 - 13
Basically I need to write a query which interrogates all the records PA0001 checks the mapping table and gives me all the records in PA0016 which do not have the expected mapping result.
So based on the above data I would expect it to return only the non-matched data, which will be:
I would probably need to do another query which does the exact same thing but the other way around. It would need to interrogate all the records from PA0016 and using the mapping table check the records in PA0001. In my example above I would get the exact same result, but I suspect with large amounts of data, I would get different results, especially if the number of records were not the same in both tables.
I suspect I would need to write some SQL code or VB code to extract the above.
If one of you kind folk could give me some basic pointers that would be great.
I have a store database containing multiple values about a single product, and separate table on color that this product comes in and in another table are quantities this product is available in. How can I make relationships between the tables so it works and I can make queries from it?
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
I am converting / developing a database that stores information pertaining to individual birds and their recaptures over many years. Here is a condensed version of the many tables in this database:
tblIndividual Bird: Autonumber (Primary Key) Band Number - also, unique to the individual bird Sex - M or F etc ..
tblCaptureInformation: Autonumber (Primary Key) Band Number - look-up from tblIndividualBird (using hidden Primary Key) Capture #- # which indicates what capture this is (ex. Intial capture - 1) Place Age Date etc ...
Each time a bird is captured, we record information pertaining to TIME, MEASUREMENTS, and NEST INFO. So, I have seperated the data based on these headings and made them into individual tables.
Now, my problem .... I have already created a relationship between CaptureInformation and Individual Bird. However, in the last 3 tables I would like to create a drop-down menu which shows the Band Number and Capture Number and make relationships there. What is the easiest way to do this? As of now, when I make a look-up field in the last 3 databases to show this info, the Band Number comes up with the Autonumber (because I am using the CaptureInfo table) which does not really help someone entering the data. Thanks for your help.
I am trying to create a db for service orders for customers. At the moment I have four tables, customer, service_order, parts and totals.
I have one form for customer records that has a button that when clicked opens another form for that customer's service orders. The service order form has two subforms, one for parts and one for totals.
When I try to add a new service order for my test customer it says "you cannot add or change a record because a related record is required in the table 'customer'.
As you can see here (http://www.abstractmusic.org/relationships.gif) I have three relationships setup. cust_no in customer table is a PK and so is service_order_no in service_order table.
Also I am having problems with the totals, as the fields are from different tables the equations won't work from within the subform (I guess I need some kind of query). I need the totals in a seperate table other wise I have a total for every part entry.
I am trying to set up a database to detail dances published in a magazine over the years.
I currently have all the information in an Excel Spreadsheet but know that Access would be better.
The columns in my spreadsheet are:
Dance Choreographer(s) Level Count Date Published Song 1 Artist 1 Count In 1 Song 2 Artist 2 Count In 2 Song 3 Artist 3 Count In 3 Song 4 Artist 4 Count In 4 Song 5 Artist 5 Count In 5 Song 6 Artist 6 Count In 6 Song 7 Artist 7 Count In 7
There can be two or more dances with the same name The same choreographer(s) could have written more than one dance The same count can be used for many dances About 15 dances are published on the same date One artist can have more than one song used One song can have more than one artist singing it One song and relevant artist can be used for more than one dance
I tried using Access For Dummies but it has confused me even more. I cannot work out what tables there should be and what relationships.
Not all dances have 7 songs for it - some have 1, some 2, some 3, etc.
What is listed as song 4 for one dance could be song 1 for another or song 5, etc.
I'm a novice and I'm confused. Maybe it's the way I think. I feel like there is an easier way that I'm overlooking, but I can't seem to get a satisfactory solution.How would YOU create your tables/relationships if you had the following:* The general purpose is to manage orders* You have to store information about the order (like order number, date)* You have to store information about from what company the order is from (like address)* You have to store information about from which department of that company the order comes from, each department has their own information that needs to be stored (like contact person).Keep in mind that you don't want to memorize which department is from which company nor do you want to be able to make the mistake of entering an order from a department that is not a part of that company.It seems like it should be an easy thing to do, but I'm stumped. I've thought about creating a new table for every company with a sub table for every department but that doesn't seem very practical. I tried creating one table called Company and one called Department, then merging them on a third table which is then linked to a fourth table called Orders. I'm not convinced this is the best way to do it, but it's my best guess at this moment.All help is greatly appreciated.
i know tecnically you can create a table with no relationships but is it "ok" to do so?
im using a table to store some values which are only referenced through a query but it is completly detatched and has no relationships with any other tables, im awear my database will function perfectlly happily but is it an acceptable programming standard?
I am creating a database of medieval labor contracts and have come across an issue.
I have a table of Contracts, and a second table of People. I want the table of People to show every contract in which that person appears. Each contract has multiple roles - there is always at least a Laborer and an Employer.
The same person might appear as a laborer in one contract, and an employer in a second contract and I want my People table to pull every contract in which that person appears, regardless of the role they play in the contract.
So far I have not been able to get this to work. I set up two different one-to-many relationships which link the People table primary key (personID) to two separate columns in the contract table. However, in the People table, instead of pulling contracts in which the person appears as either Laborer or Employer, it will only pull contracts in which the person appears as both Laborer AND employer (a situation which will never occur in my actual data but which I tried out as a test).
I have one database called asset management. It consists of one main table called cyber assets. Most fields in this table are linked to a manually created lookup table inorder to restrict user input. There are also two additional, none lookup, tables used to list a) the IP addresses (there can be more than one) and b) another similar 1 to many type table. Basically this DB is used to manage basic cyber asset data, excluding most items related to configuration management.
So, this above DB serves the purposes of asset management. Now I essentially need a similar DB for Patch Management. What I've done for this is to assess each patch initially (i.e. just by looking at the patch title and determining if we even have any of those device. i.e. this assessment is not based on OS, model number... just a general 'may' or 'may not' be applicable). Here's what this SEPARATE DB looked like:
Since each patch is essentially assessed against itself, or maybe a better way to describe it is against the users memory of what we do and don't have, only a single table and form was needed.
So now we've been thru this process and the DB is filled, all initial assessments are complete. The next step is to take all the ones that are applicable to our company (based on the initial assessment when you answer, yes is applicable) and do assessments based on each device we have.So what I want to do is to link the two DBs on a new table called Patches_by_device, inside the original patching DB... so the relationships would look like this:
But as you can see, the linked table CYBER_ASSETS has some sort of undefined relationship type, which is causing my issues.So the next thing I did was to autocreate a form based on the Patches_by_device table, and here's the result.I need to change the patch_key to the Patch_ID+Patch description+URL, etc, and to change the device key to the the UNID+IP+functional description, etc...so I changed the form record source like this:
Now I should be able to change the control source of the Patch_key and Device_key to more useful information. so I changed: Patch_key control source to Patch_ID and Device_key control source to UNID (which is in the cyber assets table)
As you can see, it worked for the patch_ID but not the UNID which is part of the linked table.Must it be within one DB, because we have a ton of other modules to implement (e.g. config management, vulnerability assessments, audit stuff, and more...) and I'd like all these to be in individual DBs, all liked back to the main cyber_assets/Asset management DB.I've considered just modifying that patch table so that each device has its own column heading in the table, but this will cause issues when new devices are added.
I want to build a Financial Database. We are provided a certain amount of budget under different heads each year. Every month we spend some money from some or all heads. Then we provide a detail of expenditure during the month under each head and the balance thereof. My request is how many tables I need in my database. My opinion is 5 tables each for Years, Months, Heads of Expenditure,Budget Allotted, and Expenditure.
Is this relationship correct (see attachment)? When i create an orders query with the fields:-Barcode number (source: orders/products table)Product name (source: products table)Quantity (source: orders/products table)Unit price (source: products table) Total (calculated - [Unit price]*[Quantity] )it does not allow data entry for barcode number and quantity (product name and unit price should appear automatically when barcode no. entered) why is that? and how could i resolve this? attached also is my database to see what i mean... if u have time can u please make the modifications NEEDED and send me the modified one, if not just give me a textual description of what i should do..... (btw it is actually a school canteen system databse, thats why i have customerID field as student administration number or staff initials)please reply soon.. this is urgentthank you
