Normalization And Relationships - Don't Know How To Place Data
May 31, 2005
Hi, I want to create an access app that will allow a user to see the number of pages printed by various printers.
These are the data that I have:
Model Type (various are available)
Printer's name (unique to one printer)
IP addr (unique to printer)
Quantity printed (such as Single Legal, Double Legal, Single Letter,etc)
This is what I have (2 tabels with 1-many relationship):
Printers
PrinterID
Model
Name
IP
ModelTypes
ModelID
Model
Now, where do I place the quanties of paper printed? Do I just leave it in Printers?
I have a simple modeling question. Lets say I have the following table structure:
Table 1 ------- Cust_id Cust_name Phone City Supplier_id Dept
Table 2 ------- Supplier_id Supplier_name phone City
Table 3 ------- Dept
Table 1 is M-to-1 with Table 3. Table 2 is 1-to-M with Table 1.
I have seen such as schema and do not understand why there needs to be a separate Table 3. Can someone explain why Table 1 alone would not suffice and Table 3 would not be required?
Basically, can someone explain the rationale for normalizing tables when there do not exist sufficient associated data for the field for which a new table is created (i.e. in this example, if Dept had a lot of other fileds associated with it such as Dept_desc, Dept_Mgr etc , then I can see the value in creating a different Dept table and joining it with Cust table through a Dept_id field).
A similar such design exists in the standard "Address Book" sample schema that ships with Access (the analog field in question there is Role).
You know the scenario where people keep working in an old version of a database, while you work on an update... leaving you with a new database, but old data?
This concerns a database, containing about 20-25 tables all linked together. I was asked to make an update. In this update 3 new tables were included and here and there a few of the old tables got news fields, nothing crucial and a lot of forms were changed.
The current situation: Database 1: contains new data, misses a few tables, is missing some fields, forms and queries Database 1.1: contains old data, but some new extra tables, added fields, forms and queries
I would like to know if it is possible to import all data from 'database 1' into 'database 1.1', so that all linked information between the data is preserved, all database relations are maintained.
If I import tables from Database 1 into database 1.1, tables just get added, not replaced. If I delete or rename tables, I loose my relationships.
I would like to know what is a common and effective way to get all the new data of an old version of a database (where people keep working in while you work on an update), into the new version of the database, that currently contains old data.
I have a relationship between Market data table and Trade table which is One to Many. Market on the One and Trade on the Many. However I just found out that I am unable to enter information into the Trade Form without entering information into the Market Form first. I would like to enter info. into Trade Form/tables independent of whether I enter info. into the Market Form/table, but I still need to have the relationship of One Market data to Many Trade date.
I have 2 excel sheets that I have imported as external data. I created a relationship between the 2 tables on part# field that is common to both. The relationship is to take all info from A table and only those from B table that match. The part# field is set to text in both tables. When I run a report that merges the 2 together a significant # of parts in my field change to a negative number. For instance the part# in both tables are 12345678 it is changed to a random -3452345 number..
I've worked with Access in the past but haven't touched it for 6 years now so I've forgot a lot of the things i've learnt. Bare with me while I try and explain it as clearly as possible
I'm creating a Database for work which will register all our Commerical Clients, which projects the clients are related to and which students worked on them projects. So I have 3 main Tables:
1.Commercial Clients Client ID Company Name Contact Title
2. Commercial Projects Project ID Nature of Project (Graphic Design, Multimedia, Media) Project Name Project Information Staff Member Responsible Start Date End Date
3. Students/Graduates Student ID Student Name Area of Expertise Contact Details Last Project worked on
Right here we go.....
Each commercial Client can have more then one project to their name. Each Project can have more than one student working on it and each student can be working on more then one project.
My relationships are set up like this:
Commercial Clients <One to Many> Projects Projects <Many to Many>Students
What I have done is create forms with sub forms in to enter to extra data. I dont know if this is the right way to do it but it seems to easiest way. Please show me a way that will work better if you know it.
Anyway... the way the tables are now I can on cascade the information down through the tables.... it will not cascade upwards. I have ticked everything you can possibly tick for all relationships. So when I can enter projects in the Subform on the Commerical Clients Form and I can enter students on the subform in the Projects form. But on the Student form the projects the students at on does not show up in that subform and entering the day only tries duplicating it (which i dont want to do). If I had a form that showed the projects and the related commercial clients in the subform it wont find the commerical clients. So i.e.
It shows fields when the cascaded down like:
Commercial Clients V V Projects V V Students/Graduates
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never). I have a form with several tabs, linked to a table with employee information, works like a charm. Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings. Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab. I definately don't want any relationships with the employee table. Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here. Much appreciated,
My problem is displaying data in many-to-many relationships.
There are three main tables: tblShops, tblOwners and tblMarketingEvents.
These are linked through two junction tables creating two many-to-many relationships:
1) Each Shop may have more than one Owner and each Owner may have more than one Shop. 2) Each Marketing Event may reach more than one Owner and each Owner may be reached by more than one Event.
Now I want to display for each Marketing Event:
1) the data of that event, (tblMarketingEvents) 2) plus the owners reached in that event, (tblOwners) 3) plus all the shops owned by the owners reached in that event. (tblShops)
Preferably, I want to display more than one record at a time without repeating any information.
Is there a way to display the data like this?
I tried a query: it gives the right data but does not display it as I need. It repeats the data from tblMarketingEvents and tblOwners for each shop.
I tried a form with subform: it can only display one record at a time and is hard to work with (printing etc)
I tried a report with grouping levels based on the query: Again, right information, wrong display. I need to group according to the records of the MarketingEvents table and not just according to a single field.
I have created a form with two subforms. The context of this is a litigation database. The main form is for testimony. Among other things, it includes a field for a question and an answer. Any given piece of testimony may relate to one or more documents, and any document may be referenced in one or more pieces of testimony. So have two subforms, one for documents produced by the Plaintiff, and one for documents produced by the Defendant. It worked out easier that way because I originally created indices of those documents in separate Excel tables which I then imported to Access.
So the relationship for both document tables relative to the testimony table is many to many, which is established using two junction tables.
I also have a combo box in each subform where I can autofill fields based on selection of the document number.
My problem is that while the combo-box works great, once I select a document in the subform, that selection is carried over into the next entry of the master form, but also the selection is not retained in any of the master forms. So I can browse the document listing from any entry in the master form, but I can't get the document selection in the subform to stick and create a lasting relationship to a given record in the master form.
Is my problem that it is just impossible to have a subform based on a many to many relationship, or is it in the parent child relationship of the form and subform, or is it in the properties of the subform?
I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.
tblLangInfo: LangID (PK), language name, language family, etc.
tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in
tblSegmentLangJoin: LangID (PK), SegmentID(PK)
I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:
tblProcesses: ProcessID (PK), Process name
tblProcessLangJoin: LangID (PK), ProcessID (PK)
Here's the fun part...
I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:
This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.
The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.
E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.
I've attached a picture to illustrate the relationship I need, since that's likely clearer.
One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.
I have a DB setup with users and their information. How can I remove a user from the DB without losing their data?
Basically I just don't want the users seeing this (deleted) user in the list on a form. So maybe "hide" is a better term than delete.
Details: Access 2010
Table "Employees" - holds username and some other user specific info. Table "Hours" - holds username related data(how many hours they worked on specific dates). Form "WorkHourEntry" - The form I use to allow the users to login and enter their data(hours).
Then have a combo box that displays all the users in a list. This list/combo box is where I want to hide users from when the employee leaves the company or dept. and is no longer needed. But, we need to keep their history/data.
I'm attempting to normalize an enormous table with order data, but I'm running into some problems. The table currently contains many duplicates, of which also included the actual order information (yikes!), but I managed to normalize it almost all the way down. It appears that different accounts can be used on orders, and these order numbers are being recycled for some reason months down the line (don't ask my why they're reusing them for future orders because I have no idea either, they should be creating new order numbers). Of course, the Order number is the primary key in my table as it should be. I guess the same thing can occur with the sales rep. Anyway, I'm struggling to find the "best practice way" to deal with this situation. I'm almost tempted to create an intermediary "transaction table" or something like that between the main general order information (which at this point will basically be the Order Number and Customer ID only), then include a table with the account information and sales rep info, then have that link to the Order Detail with the products, quantity, order number and various dates for those order numbers. Order maybe it should be a separate, related table, but not between the general order information and the order details? Can anyone tell me if I'm on the right track for this situation? It was a total curveball that the rep and account information could be different on these orders.
Option 1: Order (Order #, Customer) -> Transaction Information (Order #, Account Type, Sales Person) -> Order Details (dates, products, quantities, etc)
Option 2: Order (Order #, Customer)---> Transaction Information (Order #, Account | Type, Sales Person) | |-> Order Details (dates, products, quantities, etc)
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
ok im working on a database that a company sends its employee to school and tracks there training.what classes they take what not.. my tables were fine until i realized that supervisors were also employees so i need to add a supervisorID i dont know how to do it without screwing up all my foreign keys i assume it goes into my employee table and department table but then where do i put my departmentID foreign key?
I currently have a database which I believe is in 3NF. It's completed as such, but I have recently come accross normalization methods. Because of this I would like to know how my 3NF tables could look in 0NF, 1NF and 2NF. For both the purpose of ensuring the database is correct now, and for future reference...to do a more professional job next time!!Here's how the tables currently look:CUSTOMERS(CustID, CustName, CustAddress)ORDERS(OrderID, CustID, OrderDate)ORDERDETAILS(OrderID, BookID, BookPrice, Quantity)BOOKS(BookID, AuthorID, BookName, BookUnitPrice)AUTHORS(AuthorID, AuthName, AuthAddress)It's just basically a simple book royalty system. I have looked around various sources for examples, but my database has a few more tables in 3NF than most examples show.Thanks guys.
For my job history entity should I just store my placement number and remove the candidate number? Since placement number already determines the candidate number in the placement table.:confused:
I have decided to normalize further and now have brain freeze. I have a table 'tblComputers'. I have removed Computer Make + Computer Model to own tables and linked via ComputerMakeID & ComputerModelID, this is working OK.
I then decided to remove RAM to its own table. I created tblRAM. I added additional rows in tblComputers 'Memory Slot 1' Memory Slot 2' etc. tblRAM has 2 columns RamID & RAM so....RAMID 0 = 0 RAMID 1=128 RAMID2=256 etc..
My issues? What to link RAM to relationship wise then allow users to select multiple instances for different memory configurations to save values back to 'Memory Slot 1' 'Memory Slot 2' in tblComputers?
The first table contains Employee ID, and Name of Employee
Second table contains Employee ID, and an autonumber
The third table contains an autonumber and a unique PIN
The 1st/2nd table are connected by Employee ID, the 2nd/3rd table connected by the autonumber. Now not every Employee gets the unique pin and the 3rd table gets re-done every year. This structure works great for all "current" employees. Employees that leave get deleted out of the 1st table, thus causing them to get deleted out of the second table due to relationship. This becomes a problem because I need to keep a record of who had what unique PIN, regardless of who is still assigned one or not. Anybody have any ideas on how to do this.
I'm not a programmer and know very little about setting up access databases other than the very basics...however...due to my politics background, I'm setting them up for the local government....just my little disclaimer about why I'm asking stupid questions.
I am creating a database for multiple users who are of two sorts. The users deal with either the setting up of a contract, or with the payment of a contract. Both of these groups need to be able to access different information about the same contracts. What seems most logical to me is to create two tables in the same database, one for the contract, and one for the payment information and have both update when one or the other is worked on.
My basic set up for now is this: Table One: contract information Table Two: Payment information Link Key: Serial Number from contracts These are my questions:
1) is there a way to set this up so that the only way someone from the payments group can create a linked record is if a record with the same serial number already exists in the contracts table? 2)Is there a way I can set it up so that when someone in the payments group starts to input their information, they can find the serial number they want, and have certain fields show up? For example: I want them to be able to search for a serial number and have the contract name show up with all of the previously entered payment information, then have a new field each time they go into it for their next payment (if that makes sense). 3) When linking, hopefully in such a way as to do the above, do I need to have the same field name in both tables, or...? I'm a bit confused as to how this works as initially i created a field in both tables that would have the same serial number and tried to link those..and it didn't work.
Sorry if this is so confused...I'm sort of learning as I go along.
I am having some questions on a order entry DB I'm working on, (modifying an existing one). I've attached a shot of the current relationships to demonstrate. Basically to start this was an order entry db to place orders to one vendor, so this was not a problem then... But.. Now I am modifying it to order from more then one vendor. So, I have the order form. You first pick the Vendor the order is going to, then the Customer and delivery site. Next you move to a subform to pick your items from a dropdown. So the problem... I have a new table for the Vendors. This table is related to the "Order Table" on a one to many. But what I also need is the VendorID to be in the table "tblItem" Thus when the Vendor is picked in the main form ,it can be filtered in the subform to only show items from this vendor. Now this would work by placing the Vendor ID in the "tblItems" table and relating the two. But would this be proper normalization to have the VenforID as a FK in two tables???
I am putting 80 rows (personnel) of data with about 20 columns (training courses), to track training for my people. Is this enough that I would need to make several tables, or am I correct in thinking I can put it all in one table? From what I have been reading, this is a pretty small table, but I want to make sure.
Hello all, I have been tasked to create a database to track tooling information for my company. I have a solid idea on the process involved and would like some help getting my tables and relationships setup with normalization. I have noticed in the short time I have been learning access that normalization is the key to happiness.
A short synopsis on how this should work: A model is built from many different parts, these parts are sometimes (usually not always) associated with the tool that is used to build them. A tool could be used to make several different parts and some parts use several tools. In addition to this, a model can use a different quantity of parts to build it and those parts can require different quantities of tools to produce them.
I have tried several different ways to relate these tables together, and would like to ask the braintrust that is this forums for any help they could give me.
I have a contract-tracking database that has been in use for about a year. It used to be that every contract had the same number of periodical "applications" with it - the companies we worked with would send in 4 applications over time.
So, I had this set up:
tblCompanies (the companies with whom we work) - CompanyID (Primary Key autonumber) - CompanyName - Address, contact info, etc.
tblContracts (the contracts we issue) - ContractID (Primary Key autonumber) - CompanyID (foreign Key) - DateIssued
tblApplications (things companies send back to us over time) - AppID (Primary Key autonumber) - AppDesc (description of the application - something like "Application 1", "Application 2" , etc.
tblAppsRecvd (Join table/log of apps received) - ContractID (foreign key to tblContracts, so we know which contract this applies to) (also a composite primary key, so we don't duplicate an app to a contract) - AppID (foreign key to tblApplications, so we know which application we rcvd) (also a composite primary key, so we don't duplicate an app to a contract) - DateRcvd
Pretty self explanatory. Everything was keen, and I was reaping the rewards of a job well done.
But now we've expanded out contract offerings. He have introduced 2 new types of contracts, and each contract has a different amount of applications associated with it.
Contract 1 has 4 apps. Contract 2 has 2 apps. Contract 3 can have 1 to 8 apps. (The last app before a contract closure is always considered "Final" - so if we get 5 apps, it would be apps 1, 2, 3, 4, and Final.)
I've tried implementing a couple different schemes, but none seem to be fitting the bill. The type of contract will dictate how many apps we receive - i.e. If we are using Contract 1, then we KNOW we'll recieve 4 apps over time. So, is this as simple as adding a "ContractTypeID" to the tblContracts with an appropriate tblContractType table, and expanding tblApplication to more apps, or is there a smarter scheme?
Im setting up a database that contains information about printed circuit boards that are being developed for our current project. The information that i need includes the following:
Board # - 3 digit number Board Name - Module # - 9-digit number Module Name - Project Leader - engineer responsible for design In/Out/Oem - inhouse, outsourced, or purchased OEM Designer - engineer or outsource contractor who designed the board MFG/Assembler - company responsible for building the boards Directory - file on network drive the drawings are located in
There can be mutiple boards in a single module. So i have 3 tables right now.
pcb_tbl - this includes all the info above minus the module info.
Module_tbl - this table includes the module number and name and responsible engineer, with the module number being the key.
module_pcb_tbl - this table lists the module number in the first field linked to the module table, and the pcb number in the second field linked to the PCB table.
I have a PO DB that I'm trying to normalize. For the most part it is normalized but there are 2 fields that are not normalized because they repeat data. So I'm trying to restructure a bit and having some problems when it comes to data entry via forms.
There is also a PO table above this one but is not used in the subform for the details. My old set up for my PODetailsTable
PODetailsID pk PO fk Item Quantity Price Allocation
I want my new set up to be PODetailsTable
PODetailsID pk PO ItemID fk Quantity Price AllocationID fk
ItemTable ItemID pk Item
AllocationTable AllocationID pk Allocation
On my form I can't enter anything that is already on the ItemTable. I looked at some microsoft sample DB's and they have you enter the item from a different form then you can select it in from the combo box which would really slow down data entry peticularly early on.
Is there a way I can type the Item in and if its new, it will be put it on the ItemTable associate an ItemID to both tables?? But if its already in the Itemtable just apply the already existing PK on ItemTable to the FK ItemID on the PODetails Table for that record??
Here is a A2K format of it which is how it currently works. In the relationships window you can see an example of how I want it but can't get to work.
