Some Access Questions

[amegahed3]

Hi All,

I'm not an expert in Access, and appreciate it a lot if anyone can help me answering the following questions/problems (I use Access 2007 by the way):

1. How can I export the tables structure (including all relationships) to a word, visio, similar office file or a pdf file?

I tried using the "relationship report", but apparently my database is too large, that I got the message: "the Relationships window layout is too large to fit in a Microsoft Office Access report. Some of the tables and/or relationship lines will not show up in your report. To print all relationships make the layout smaller and run the wizard again."

I tried to make the layout smaller, but couldn't.

Any help?


2. When I go to relationships in order to add a relationship between 2 tables, I am not able to display all tables in that relationships view, since I have so many tables in my database. Is there a way that I get Access to display 2 specific tables (and may be not the rest, so that it is easier to view), so that I add a relationship just between those 2 tables?


3. Apart from the method in my question no. "2", is there a way of creating relationships between two tables other than opening the relationships view and dragging a relationship between 2 tables?


4. For one certain table, a certain field (period ID) has to be in order, i.e., whenever the user inputs certain records, he has to make sure that that field is ordered in sequence, i.e. he inputs the record for when the periodID is 1, then that for when the periodID is 2,....etc. Is there a way that I enforce the user to do so?

The only way I see is to write a macro that checks the order, after the user inputs them, but that wouldn't even work perfectly for me; since the user might skip record(s) for certain PeriodIDs, which is not allowed, i.e. he might enter the record for when the periodID is 1, then that for when the periodID is 3, and skip that for when the periodID is 2.

Is there a way to ensure that?

Thanks a lot in advance.

Aly

 

[HiTechCoach]

1. How can I export the tables structure (including all relationships) to a word, visio, similar office file or a pdf file?

I tried using the "relationship report", but apparently my database is too large, that I got the message: "the Relationships window layout is too large to fit in a Microsoft Office Access report. Some of the tables and/or relationship lines will not show up in your report. To print all relationships make the layout smaller and run the wizard again."

I tried to make the layout smaller, but couldn't.

Any help?

Have you tried using the buikt in database documentor? Iy will generate a report for you that you can print to a PDF or save to Word.

If you have Visio, I think is in in the enterprise edition, it can reverse engineer the database.

2. When I go to relationships in order to add a relationship between 2 tables, I am not able to display all tables in that relationships view, since I have so many tables in my database. Is there a way that I get Access to display 2 specific tables (and may be not the rest, so that it is easier to view), so that I add a relationship just between those 2 tables?

*** make sure to make a backup first ***

It is possible to delete table from the view without deleting the actual relationship.

What concerns is that that you has so many tables. This is usually a sign of database normalization issues.


*** make sure to make a backup first ***
You should always make a back up before making any table design/relationship changes!


Every time I have seen a database needing so many tables and relationship, there was alway ways to reduce the number of table by applying the rules of data normalization to get to 3NF.

3. Apart from the method in my question no. "2", is there a way of creating relationships between two tables other than opening the relationships view and dragging a relationship between 2 tables?

You can use the edit relationship dialog.

Again, so many tables and relationship seems extreme.

4. For one certain table, a certain field (period ID) has to be in order, i.e., whenever the user inputs certain records, he has to make sure that that field is ordered in sequence, i.e. he inputs the record for when the periodID is 1, then that for when the periodID is 2,....etc. Is there a way that I enforce the user to do so?

The only way I see is to write a macro that checks the order, after the user inputs them, but that wouldn't even work perfectly for me; since the user might skip record(s) for certain PeriodIDs, which is not allowed, i.e. he might enter the record for when the periodID is 1, then that for when the periodID is 3, and skip that for when the periodID is 2.

Is there a way to ensure that?

I am not sure a macro can hadle this. I do know that that VBA code will handle this.

I would writ VBA code so that the ID is automatically assign for the user.

To create you own custom auto-number, you an use the DMax() function to get the last entry and add 1.

What happens if the user deletes a record in the middle of a series leaving a gap?

 

[amegahed3]

Hi!

Thanks a lot for your reply. All what you said made sense and was very helpful. My questions/comments now are as follows:

1. You said that the big number of tables and relationships is extreme, and that I can use the "rules of data normalization to get to 3NF". What is that exactly? I mean, could you tell me more about solving that bug of mine (having so many tables and relationships) ?

2. As for my last issue, you asked about what happens if the user deletes a record in the middle of a series leaving a gap?

Well, that should not be allowed.

So, given that and the previous, what would be the best thing that you would recommend?

Thanks a lot. I really appreciate your help!

Aly

 

[HiTechCoach]

Hi!
1. You said that the big number of tables and relationships is extreme, and that I can use the "rules of data normalization to get to 3NF". What is that exactly? I mean, could you tell me more about solving that bug of mine (having so many tables and relationships) ?

Learning how to proper design a database using the rules of normalization comes from years of experience and studying the theories. After years of working with the same database and adding features, you learn a lot from your mistakes.

I can point you to lots of reading if you would like.

To give you any specific advice on how to improve your actual database design will require taking a look at your database with sample data in the tables.

Hi!
2. As for my last issue, you asked about what happens if the user deletes a record in the middle of a series leaving a gap?

Well, that should not be allowed.

Hopefully the humans will never make mistakes.

 

[amegahed3]

Hi,

Thanks a lot for your kind reply.

As for the problem with my database, first, yes, sure, I'd appreciate it if you could suggest some readings about that for me. Second, it would be REALLY great if you could look over my database and let me know of your suggestions. Could you send me your email so that I send it to you (as I really do not want to make it available in public)?

As for the specific Periods problem, isn't there a way that we "force" the user not to do such unwanted problems?

Thanks,

Aly

 

[HiTechCoach]

Hi,

Thanks a lot for your kind reply.

As for the problem with my database, first, yes, sure, I'd appreciate it if you could suggest some readings about that for me. Second, it would be REALLY great if you could look over my database and let me know of your suggestions. Could you send me your email so that I send it to you (as I really do not want to make it available in public)?

As for the specific Periods problem, isn't there a way that we "force" the user not to do such unwanted problems?

Thanks,

Aly

Aly,

I will PM you my email address where you can send the file(s) Be surew to compact the database(s) before zipping.


To know where to pint you, how much database design experience outside of Access do you have?

 

[amegahed3]

Thanks a lot. I really appreciate your help. I already sent you an email with my database, and would appreciate your comments about it via email.

I don't really have much experience in databases. So, how would you direct me in that case?

Thanks,

Aly

 

[HiTechCoach]

I will take a look at your database. That should give me a clue about what further reading may help you.