Too Many Relationships / Indexes
Apr 21, 2006
Hello
Sorry to add to the number of 'relationship' posts but a thorough search has failed to turn up anything similar to my problem.
I have a table tblSite
Each geographical site (defined by a PK 'SiteNo') in tblSite can contain multiple occurrences of a large number of types of geographical features (ie multiple 1-many relationships).
Eg each Site can contain
100 trees
10 ponds
5 streams
2 springs and so on.
I could have anything up to 50 or so different types of feature that are related on a 1-many basis to the site, linked by 'SiteNo'
Access prevents any more than 32 indexes per table so in practice that means that you can't have more than 32 relationships with referential integrity enforced on any one table. I thought that I was correct in having a separate table for each feature type and creating 1-many relationships to the central table. The fact that I can't have more than a certain number of relationships suggests that my structure is wrong.
I thought about having one large table with a 'feature type' field plus all the other fields required for each feature type but the info recorded about each feature type is different, leading to a large table with many fields, many of which would be blank. This seems to be un-normalized to me.
So, my question is twofold really.
Have I got my structure right? Is it one of those special cases (we all want to be a special case?) and if so, how do I get round it?
If not, where have I gone wrong?
I thought I was comfortable with my design but coming up against this limit has thrown me a bit and I'm not sure how best to proceed.
regards
Neil
View Replies
ADVERTISEMENT
Dec 11, 2006
I am a beginner and can someone please help me with creating indexes( I think this is what I want to do)
I have a table for entering realtors
the primary key is realtorID (AutoNumber)
is there anyway to find if the realtor has been entered twice or prevent the realtor from being entered twice
thanks
Josh
View 1 Replies
View Related
Sep 17, 2004
I am trying to link a Progress table into Access. However, the Progress database table has 41 indexes. I have even tried to import and get the same error - Too many indexes - . Is there a way to get around this? Currently I am importing to Excel then linking Excel to Access. The table is time sensitive so a live link is the best. Any ideas? Thanks
View 2 Replies
View Related
Nov 30, 2005
I am a beginner when it comes to access. The project that I am working on is tracking Travel claims. The claims are submitted ever month for 4 years so I have created 48 tables for each month and tried to link them via the SSN. The member can also submit other claims during this period, ie supplementals (for reimbursements that were missed in the monthly) and Concurrent duty claims (for the member going to additional sites then where they are assign).
I have a
MBR tbl - that holds all the MBR's info (SSN, name, unit, city, state, POC, POC email and Remarks)
(48) Partial tbl - linked by the SSN.
(5) Suppplemental tbl - Linked by the SSN ( I need more of these but I started with 5)
(5) Concurrent tbl - linked by the SSN ( I will need more of these)
These are all one-one relationship.
How can I get this to work. I can upload a blank copy of this database.
Thanks for any help
Craig
View 2 Replies
View Related
Jun 18, 2005
Hi guys. I got unbounded form that I want to add searching functionality by utilizing indexes. I would like to know how to add indexes for my customer table so that I can use it for searching records. I be happy if some one help with make indexes. Thanks
http://i5.photobucket.com/albums/y1...tabledesign.jpg
==> pic of my customer table in design view
View 1 Replies
View Related
Jul 15, 2015
I have sheet names stored in a text field.
sample data: (assume the field name as SheetName)1 (considered as an index) MySheet (considered as a Name)
How to handle them both in a single statement:vartype(SampleData!SheetName) returns 'Text' as Datatype for all values.
How to consider the numbers as indexes?
View 3 Replies
View Related
Mar 18, 2014
I am trying to consolidate a few tables I made to make them less numerous and hopefully easier to handle. At one point I received an error message that said I had exceeded the maximum number of indexes in a table (32) and thus it could not be saved. When I scroll through my table in design mode every box that has "Indexed" as an option is marked at "no". Many boxes do not have that option and thus I am not sure if those are automatically indexed(?), not indexed(?) or that indexing is not relevant for those values(?). I definitely do not have any, let alone 32+ boxes, that have "indexed" checked to "yes" though. They are all "no".
How do I figure out which columns in my table are indexed otherwise so that I can reduce those numbers and get more columns on my tables (by the way, I am no where near the limit of 255 columns on any of my tables)
View 2 Replies
View Related
Oct 5, 2012
I cannot cause a database to refrain from dropping several table indices during a Compact on Close operation. I am running Access 2010 under Windows xp.I have created a database that merges data in a linked text file with data in a linked database, appending the results in a local table. The database has the Compact on Close option set. The table has 27 indices (because most of the fields either are coded or are potential select or sort fields) and nearly 1 million records.
I run a delete query to clear the contents of the table, I close (and compact) the database, I reopen the database, and I run the append query. The indices in the target table are intact. I close (and compact) the database again; when I reopen the database, indices are missing -- sometimes about 10, other times nearly two dozen!
This table is linked to a larger system, which contains code to run the sequence outlined above. Whether run manually or in code, as stand-alone or as a component, indices are dropped. When I close the database with the table containing no records, the indices are not affected. After appending records, but before closing, the database contains 1.66GB. The drive on which the database resides has 42GB free.As suggested with regard to a number of other unexplainable issues, I have created a new database and imported all objects from the original database. The results persist. The larger system performs accurately without the indices, but the performance against a table this massive is horrible.
View 8 Replies
View Related
Dec 3, 2012
In month-end posting - I basically delete all the records from my transaction file. And in doing so - it seems to be resetting the auto number index - which is causing me major problems.
The resetting of the index after deleting all the records is normal.
I am working in VB 2010 - using a 2003 MS Access database.
View 4 Replies
View Related
Aug 7, 2007
This is a very simple problem most likely for the masses, but I am new to access. I have employees who enter will enter information about specific tests on electronics components into separate tables by a form. I know my method is poor, but this is how it works. I got assigned this database at my internship this summer because they ran out of thigns for me to do.....I've never even used access before, so as long as it works, they're happy. I have a form which which writes to a first table with 20 fields. I then have separate update queries which take the data in the first table and put it in all the other tables. Trust me, i know this is pretty much the opposite of the whole point of a relational database, but I am and was limited by time, for the size of the database they want, there was no time to learn about normalization etc etc. Sorry this is wordy, but THE QUESTION is......if they run multiple tests wtih the same information for some of the fields I have fields named "Run#, Unit#, Date" etc, even if there is records wtih the same information, hwo do i get it all to show. Right now, it seems to be rewriting over the same records. If they run 3 tests on unit 10 on August 1st, how do i get it so all those show up. I think its somethign wtih in the table, for the primary keys, changing the Index: No, Yes(Duplicates) Yes(No Duplicates) but I could be way off. Thanks, if the question doesnt make sense i'll try to rephrase it, I apologize I'm running on about 45 min of sleep:confused:
View 3 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
