My First DB and it's a BIGGIE

[LowRAM]

Hello and greetings everyone. I am new here and have several questions. I have read and read and studied and studied, yet I am so hung up here. I am trying so hard, if you guys could help any at all it would be greatly appreciated. Once I get the hang of this I would glady contribute to the forum... I love doing visual step by steps for people to help them learn. But with this project I need visual step by steps I have learned A LOT here, but am hung up on RELATIONSHIPS and JOINS. Here is my biggest issue...

I have a very large database to develop - my database will consist of many tables including TBLPERMITSSF, TBLPERMITSMF, TBLPERMITSMISC, TBLADDRESSES

TBLPERMITSF
* SFPERMIT#
STREET#
STREETNAME
(AND VARIOUS OTHERS)

TBLPERMITMF
*MFPERMIT#
STREET#
STREETNAME
(AND VARIOUS OTHERS)

TBLPERMITSMISC
*MISCPERMIT#
STREET#
STREETNAME
(VARIOUS OTHERS

TBLADDRESSES
*STREETID
STREET#
STREETNAME

I need to be able to link or relate these tables so when our ACC department searches by address (street# and streetname) they are able to see all permits released for that address.

Example: SF1234, 123 Street South, MISC#122, MISC#123, MISC#124

Please help me -- I LOVE a good challege, but Access has given me more than I can handle so far. Its work related if you were wondering . Apparently they thought I was a lot smarter than I am I do beleive I will LOVE working with access once I get a knack for it -- and I love to help train and teach others things that are challenging like this.

THANK YOU in advance

 

[neileg]

Your design is wrong. You should hold one table for all the street addresses and one table for the permits. There should be a record for each permit issued, with a field to show which type of permit and the street address ID.

By the way, don't use special characters in your object names. Stick to 0-9 and A-Z. You can use the underscore _ if you must. Anything else will cause problems sooner or later.

 

[LowRAM]

Thank you for the information. The permits (there are 7 different types) - they all require diverse information that doesnt relate from one to another at all. Each may hold a variety of info. The only thing the permits have in common are that the addresses should match up -- although not all permits will match up with one another for example a SF permit may also have a Misc permit, a Propane Permit, and HOCC permit linked to it. But a MF Permit may only have a Misc permit and Propane Permit linked to it. Very different information is required for each permit. Maybe I am just not getting this. I considered in the beginning to seperate the permit types into a table, the deeper I got into it the more problems in the future I thought I saw, so I set up the tables as permit tables. So if I set up a table with the permit types in it, then I could relate it to each permit table? I am so very confused. Thank you for helping me.... I will continue to pray and work with it.

I will go make a new permit table with the different types of permits and I will also go back and rename my Street# to StreetNumber to prevent any problems in the DB. Thank you sooo much.

Just curious does anyone have any hair left by the time they figure all this out?? If you could think of any examples for me to study it would be greatly appreciated.

THANK YOU

 

[neileg]

OK, I hear what you say. If the information is truly diverse, then there is good reason to have separate tables. But you still don't need to duplicate information that is held in TBLADDRESSES.

To gather all the permits together you will need to create a query of each table and these use a union query to pull these queries together.

I've got plenty of hair, but there have been times...

A good tip I learned from this forum is that if you can't work it out with a pencil and paper, you can't do it in Access. Part of that is down to getting things clear in your mind when you start, part down to the fact that Access can't resolve a problem on its own.

 

[LowRAM]

Thank you -- I am going to slow down a little and review some sample databases where this may be used (if I can find some) to help understand more about the queries -- and reevaluate my database format. Thank you... Im going to go off and learn a little more then I will probably be back Thank you SOO much -- I really thought a lot about the advice you gave -- makes a lot of sense.

 

[ThreeCrow]

Here a web site you may want to look at.
There are a number database model you may find useful.


www.databaseanswers.org/data_models/index.htm