Got Problem With Relationships - Design
Apr 10, 2007
Hi, I'm trying to design a database for a childrens group but am having trouble entering data now I've created the tables and many to many relationships.
I normalised the child registration form which had the usual data but also contained things such as allergies, doctor details and emergency contact details. Now, when I normalised these I came out with a table for each of these 3 things linked to the child table with a many to many relationship. Looks good....but when I want to actually enter data, Access says I need the data in these tables before I can put them in the 'child' table, as these are the primary key tables. OK, most databases would probably require the admin person to select the doctor and an allergy from a listbox linked to these linked tables, but what about 'emergency contact'? There can't be a table populated with emergency contacts, before the registration form is entered.
What should I do? Keep the emergency contact and their details fields in the child table and ignore the rules of normalisation?
View Replies
ADVERTISEMENT
Jan 11, 2005
Hi all! Here is what I want to do, and what I have done so far:
I have one table called "AUTOS" This is a table imported from pc file, and tweaked to make it work well in Access. I learned alot doing this, and now have workable forms, queries, and reports being generated on this table. I learned on here not to let my users "work" with the table, only through forms. So that all works very nicely!
Here is the next step. First a simple background: We are a Used car dealer. Table "AUTOS" contains rows, each one consisting of many Fields. The main one being "Stock NO". Then all the remaining fields are vehicle info (many fields, but all relating to the vehicle type etc), cost, sales price, etc.
What I want to do is create a new table, called RECONDITIONING. In this table I want to store everything done to a vehicle after purchase. For example, if we purchase a vehicle, then change the oil, put on new tires, and detail it, I want these items in the reconditioning table. But I want them to remain linked to the "correct" stock number in the main table.
I believe I have done this, by doing this: I created a second table called reconditioning. Added the fields I wanted, the first one being "Stock NO". I made "Stock NO" in the main table autos the primary key, and then opened relationships, and linked the reconditioning table to the main table via that field.
It appears to have done what I wanted too, because if I open the reconditioning table, and type in a vehicles stock no, then the reconditioning info, then go back to the main table, there is now a + next to the row. If I click this plus, a box drops down with each item I added to the reconditioning table. So far so good!
Here is the main question: Now that I have this info in what seems like the proper fashion, how do I work with it?
I need to create a form, containing elements from EACH table. The design I have in mind is like this:
Across the top, I want vehicle info:
Stock No Year Make Model Date In Cost
5922 1994 Ford Focus 1/1/05 1000.00
Then under that, I want a "table" in a form, which pulls from my "reconditioning" table, listing each item that has a stock No that matches the one on that record:
5922 Replaced Windsheild Wipers 1/8/05 20.00
5922 Tuned Up 1/10/05 75.00
Ok, now, I want to allow my users to add to that on this form, to type in new entrys. I think I got the idea how to do that, but suggestions are welcome. Now, on this form, I want at the bottom, the following:
Vehicle Notes: (Just a text field for "notes" on a particular vehicle)
Total Reconditioning cost: (this is a field on the FIRST table, AUTOS, that I want to calculate by adding the total from the list above, IE in this case, 95.00)
Total_ACV: This is a field from the first table as well, which I want to calculate using (Vehicle cost: Which is in the first table)+(Total Reconditioning Cost: which is above)
Like I said, I think I have the table layout and design correct, I am just not sure of how to work with values in linked tables like I have done, and I am not sure get the right info in my list on the form, then add the totals properly.
Any suggestions are appreciated!
Bill
View 7 Replies
View Related
Dec 2, 2005
Hi Guys, thought i would pick your brains to try and help me figure out how to build this database. Any help would be very much appreciated as this one is making my head spin.
Brief::
I need to create a database that will hold information about our suppliers and the products that they stock and make.
One supplier can have a number of contacts that we speak to.
The supplier produces a number of different products.
Each of the suppliers stock certain items in certain sizes and weights and make the remainder of their stock to order, so each supplier has a number of different supply options. So in addition to the multiple supply options their are multiple sizes & weights, as well as multiple qualities.
dont know if this helps people to understand buit the products we are talking about is paper (sheets or paper & reels of paper)
example.
Joe BLoggs (Supplier) has 3 sales members (Louise, Maggie, Sue) this supplier holds some sheets and some reels in stock (Stock Sheets, Stock Reels) but they also make sheets of any size and weight between a certain range as well as reels (Bespoke Reels, Bespoke Sheets). These sheets and reels can be of different qualities (gloss, silk, offset). These different qualities can have a number of different grades (Manhattan, Triumph, Charter) and will be available in a number of different weights (70gsm, 115gsm, 150gsm).
The idea behind creating the database is to allow the sales team to query the database to find a supplier that can match the customers criteria.
I have created the following database tables
tblMainDetails
AccNo (PK)
SupplierName
Address1
Address2
Address3
Address4
PostCode
Tel
Fax
Web
Email
tblContacts
ContactID (PK)
FirstName
LastName
Tel
Email
tblStockingOptions
OptionID (PK)
BespokeSheets
BespokeReels
StockSheets
StockReels
TblQualities
QualityID (PK)
PaperQuality
tblWeights
WeightID (PK)
Gsm
tblPaperInfo
PaperID (PK)
SupplierPaperName
OurName
PaperInfo
Do these tables look aabout right???? Now comes the bit thats making my head spin Relations, can someone please give me some advice on this.
View 3 Replies
View Related
Mar 12, 2006
hey.. I’m a complete newbie at designing tables and relationships, so pls bear with me..
Im trying to construct a db that records all the details for an importing company.
Background: the company imports goods (shipments conducted by Freight Company), from a Supplier, in Containers, and then arranges Delivery of the goods (delivery completed by Haulier Company). Customs requirements must also be met.
so far these are the tables I have:
tblShipping
tblContainer
tblDelivery
tblCustoms
tblSupplier
tblFreightComp
tblHaulierComp
Here are the relationships:
1)Each shipment (tblShipping) can have many Containers, but each container can only have one shipment (tblShipping) : 1:M
2)Each shipment (tblShipping) can only have one Supplier (tblSupplier), but each Supplier can have many shipments (tblShipping): 1:M
3)Each shipment (tblShipping) can only have one Freight Company (tblFreightComp), but each Freight Company can have many shipments (tblShipping): 1:M
4)Each shipment (tblShipping) can only have one Haulier (tblHaulier), but each Haulier can have many shipments (tblShipping): 1:M
5)Each Container can only have one Delivery (tblDelivery), but each delivery can have many containers: 1:M
6)Each Container can only have one Customs Check (tblCustoms), but each Customs Check can have many containers: 1:M
7)Each Delivery can only have one Haulier, but each Haulier can have many deliveries: 1: M
These are the most important relationships (not all of them).
Where I’m getting confused is whether or not I should (or need to) create additional junction tables? If someone could have a look at the attached screenshot (of my tables with fields and pk's - but no relationships defined yet) and advise, I’d really appreciate it!
Also, im having some major probs with my variables and forms… the P/C No in the Shipping Table is the most important value here. This number is used repeatedly throughout the process. Problem I’m having is that this number needs to be alphanumeric, and also get passed to each other table (hence P/C No as a fk in each other table). How can I do this? Ive been playing around with loads of diff relationships but with no luck. I ideally need this alphanumeric number, when entered in the first form, to be ‘sent’ to other forms, as I chose. How can I do this?
Any help is really appreciated!
Thanks
snowman
ps - sorry for such a long post!
View 12 Replies
View Related
Nov 4, 2005
Hello,
I have a couple of elementar level questions on Form creation process and standards invloving a schema with several M-M relationships. I have a highly normalized design (thereby producing several junction tables). I have attached a picture of my schema to give an ideal of the schema I am talking about.
1. What is typcially the process of creating Forms on such a schema for the purpose of data-entry. Meaning, should there be several Forms (on the order of # of tables) or should there be a few that enables simulataneous data entry to many tables through one Form. What is typically the practise?
2. If there are 3 tables (A, B, C) so that A and C have a M-M relationship and B is a junction table that makes A &B and A & C have a 1 - M relationship, then how does one enter data for A, B, and C so that all the three get populated?
3. Can someone provide a copy or link to a sample database that has a similar normalized schema and Forms built into it that takes care of the data entry to the various tables?
Thank you very much for some light on this path.
View 3 Replies
View Related
Oct 6, 2014
I have had to use my first crosstab queries.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes.
I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
View 6 Replies
View Related
May 18, 2007
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
Thanks
Rahul
View 1 Replies
View Related
May 5, 2005
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3
1 5 7 9
2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN
1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2
2 d e
Table DELTA:
COLA_IND DAT1 DAT2
2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
Any comments?
Thanks in advance...
View 1 Replies
View Related
Sep 10, 2007
Hey,
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
tblLicenseInformation
License_ID
LicenseDescription
NumOfLicensesPurch
SoftwareOverview_ID
tblHardwareSoftwareLicense
HSL_ID
Hardware_ID
Vender_ID
Software_ID
AppEdition_ID
AppVersion_ID
SoftwareOverview_ID
License_ID
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
cheers
View 3 Replies
View Related
Jun 5, 2014
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column
(2) [shift+arrows] to select all of the columns I need
(3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
View 4 Replies
View Related
Jul 18, 2005
Hi all. I really need help with this. Does anyone see a problem with my relationships , i have attached a copy.
Any help will be greatly appreciated
ClaireB
View 5 Replies
View Related
Aug 2, 2005
I am new to database design and curious about relationships. In a hypothetical situation, if I have four tables: tblProducts, tblOrders, tblEmployees, and tblCustomers, how could the relationship be set up?
The Products are stored to keep track of the quantity on hand and the employees will also need to be tracked as to how many products they sell. Customers are recorded and Orders wrap up all three tables with the fields: OrderID, ProductID, EmployeeID, and CustomerID.
Would it make sense to say that many products can be bought by many customers and/or many employees can place many orders.
View 10 Replies
View Related
Aug 16, 2005
Hi
I've selected a number of tables to the relationship window. When I drag the primary key on one table to the corresponding foreign key on another table, the link created is one-to-one. I want one-to-many. Why did it create one-to-one? How do I change this to one-to-many?
Thanks
View 4 Replies
View Related
Mar 7, 2006
Hi all. I am new to the use of access and have been told that I need to create a one-to-many relationship to be able to have several records from a drop down list saved to one unique number (my primary key).
Is this correct and if so how do I do this? If it is not correct what is the best way to do what I require?
Thanks for helping.
View 6 Replies
View Related
Mar 16, 2006
Hi
I have created two tables which are linked with a one-to-many relationship
Using a form the user enters a date. Then in VB I add a value to the date and store this in the second table.
I have simplified this to try to find out what was going wrong and found that when the database adds a value to the second table which only has two fields it first of all adds it to the begining of the record set and the id field does not update to that of the first table where the relationship is joined.
Do I just captutre the value of the id field and add this to the recordset.
How to I get the database to look foir the end of record set before I appent the new generated record.
View 4 Replies
View Related
Aug 7, 2006
Hi,
I have looked for this on the help section; however, it does not seem to be covered. How would you change a one-to-many relationship to one-to-one? Access seems to automatically guess that the many side is where the foreign key.
Can it be changed from Access or is entirely on how you design your tables?
Thanks,
Bee
View 5 Replies
View Related
Aug 18, 2006
Hi !!
I am doing a school project on a school database. In my DB I have a table with all students records (eg. Student ID, Name, Surname, DOB, class etc) and I have another table where the student fees payment slips will be printed.
The fee table will only have the ID, name, surname and class fields and fee will be input for every student for every term.
I want the fee table to get all the info except the fee from the first table automatically when I give a student ID.
I have created a one to many with only Student ID fields linked, NOTHING.
Then I tried to link many fields on each side (eg Name to Name, Surname to Surname etc) but I got a msg "No unique index found for the referenced field in the primary table"
Plz help me !!
Thanks
View 1 Replies
View Related
Oct 10, 2006
would having a db with several tables and NOT setting any relationships up in the relationships window affect the db in any way?
i have such a db, which originally had all the relationships set etc. however, as i encountered errors and had to replace tables and delete entries, i must have at some point deleted all the relationships.
i just relaised that the db now has no relatioships set up (all the primary keys are still in the tables) so i'm wondering if this matters?
cheers
Jose
View 3 Replies
View Related
Nov 9, 2006
I'm trying to define relationships between a few tables in Access 2003 and It's only doing a one to one. How do I get it to do a one to many?
View 5 Replies
View Related
Nov 23, 2006
Please someone help! This is very urgent!
I am designing a database for a clinic
basically,
One Form is a Demographic Form, which was been designed. Each person was given an identifier code.
However, they all did 3 questionnaires. Each one of them has their ID code on it.
Is it possible for me to make a query so that when I search ID code, it actually brings up all the information on every questionnaire?
View 8 Replies
View Related
Jan 1, 2007
If I have a table "tblContinents" with just two fields ContinentID and Continent and I have another table called "tblCountries" with three fields CountryID, Country and Continent.
After reading several articles, i decided not to make the Continent field in the tblCountries as a lookup field but rather I will do that at form level via a combo box. In such situation, since i am not creating a lookup field at table level, do you think that it is necessary to create the relationship between the two tables in the "relationship window", or shall i just leave it as it is, and if the need arises i will create such "relationships" in queries?
Thanks very much.
View 3 Replies
View Related
Jun 19, 2007
hi, im new here and i dont really know much about databases with access. im currently doing an assignment at school requiring me to do a entity relationship diagram for all my tables, which i do not know how to do. Can anyone please assist me asap? thank you very much in advance
below is the link where i uploaded it because it was too big to be uploaded onto this post. :)
http://www.megaupload.com/?d=E6AU28CL
View 4 Replies
View Related
Jul 4, 2007
In my Db I have TblQuotes & TblQuoteItems
I want the one-to-many relationship to be based on my own generated quote reference rather than the PK/FK.
My quote reference looks something like this IN123/10
Does the character '/' cause a problem with creating the relationship as I get the error message "No unique index found" even though I know that they exist?
Thanks
View 6 Replies
View Related
Aug 10, 2007
When I view the relationships window, not one of the relationship connector lines (whatever the technical term is) do not show up! I click view all relationships, view direct relationships. I restarted my computer, restarted Access. Nothing!
I know I have relationships because when I am table design view I try to change something that I know has a relationship and it doesn't let me because it says I have a relationship.
What is going on!?:mad:
View 4 Replies
View Related
Jan 31, 2008
Hello..
Here is what I have. The database is made up of three tables (see below and see attachment). I have it set as one event to many ethnicity's and many personnel with the event ID as the PK in all three. Did I do this correctly? I thought I did and I had a form with each one of these running correclty but now it is not working.
tblEvent
EventID(PK)
tbleventEthnicity
EventID (FK)
tblPersonnel
EventID(FK)
See attachment for the other fields.
Can someone just check to see if I set this up correctly or what I should do differently?
Thanks
View 14 Replies
View Related
Nov 2, 2004
I have already made a relationship 1 to many, i want to add another relationship. I put the name of the feilds i want to join up in the drop down box. I put "enforce in..." and "Casgade update related fields" on and join type 2. Then when i press ok, it comes up with "file title " to "film title" and comes up with "no unique index found with the referenced field of the primarytable".
Does anyone know what this meens?
View 1 Replies
View Related
