Ways to get over maximum one-to-many relationships for an Access table?

[reliza123]

I have a Project table that needs to have (let’s say) 40 foreign keys because we are tracking a lot of the same stuff for our projects. Is there a way to get around the 32 one – to – many max? I found that max from: https://support.office.com/en-us/ar...ications-1e521481-7f9a-46f7-8ed9-ea9dff1fa854

Also, I can’t even use that max, for some reason, Access gives me errors after 21 one to many relationships to the project table.

 

[Micron]

I'd say there's no way your db is normalized if you have 40 primary keys in one table. Some say if you need more than one, create a composite index instead. I've seen many tables with up to four, but never more than that. I could be wrong about your situation, but if not, do yourself a favor and normalize the data.

 

[reliza123]

I'd say there's no way your db is normalized if you have 40 primary keys in one table. Some say if you need more than one, create a composite index instead. I've seen many tables with up to four, but never more than that. I could be wrong about your situation, but if not, do yourself a favor and normalize the data.

@Micron did you meant to say, "40 foreign keys !!??"

 

[Micron]

Yikes! Sorry, I read it as 40 primary. I don't believe the 32 you refer to is the number of relationships you can create in relationship design if that's what you're thinking. Since it refers to a query, I believe it is the number of joins you can make between two tables in query design, minus the indexes (not relationships or primary/foreign keys) in the underlying tables being joined. So at this point, I'm not sure if you are trying to make a total of 40 relationship joins in relationship design view, 40 joins in a query, or 40 joins between two tables in relationship design. However, I still suspect you have room for improvement in normalization. I've never seen that many in any of those aspects and I've been working in Access since 2.0 - not that I'm an expert who's seen everything. There are many Access experts out there to whom I bow in reverence!

 

[reliza123]

@Micron. I have about 21 relationships. 27 tables. and right now I am trying a Data macro and child tables to be able to have all these 21 one-to-many relationships work! I will update you if anything else comes up ! Thank You!

 

[Joe4]

I would question the structure too. There is a good chance that it may not be normalized.
Are any of these 27 tables similar in structure? If so, you need to question why there are multiple tables for those.