Question Improving my Database

[Harry_Imp]

Hi all,

In my dabtabse there is several tables such as one with duplicaes, one with out, other peoples info etc etc.

Everytime i need to add something i have to add it to each one individually, and was wondering if i could enter it one place, and it would automaticaally go into all tables?

Also if this is the case, if i were to add a set of data to my non duplicates table, and if the duplicate is already in there, is there a way where it stops me, so i dont put the duplicate in, as it would save a lot of time looking throught my databse checking if i already have this data?

I would REALLY appreciate anyones help!


thanks
Harry

 

[CraigDolphin]

You'd need to explain your database in a lot more detail, or better yet, attach an example of it along with an explanation of what you're modelling and how you want it to be useful in order for anyone to help you much. However, from the brief description given, I have to ask if you have a normalized table design?

If you haven't then you need one. If you don't know what I'm talking about, then you need to do a lot of reading before you begin to design your table structure.

You could start here:
http://www.access-programmers.co.uk/forums/showthread.php?t=100211
or do a google search using the search terms 'table normalization or data normalization.

The basic concept is that you should never have redundancy in the data you store. Enter something once, and once only. Tables should store information about one entity at a time, and there should be only one table per entity. Junction tables are used to define many to many relationships. Columns should not contain repeating data. Rows should only store key values to other table where repetition exists.

There are much more specific rules out there but until you get your head around the concept, your database will be inefficeint ands crippled even if it functions at all.
Table design is the bedrock foundation that everything else depends upon. Get it right or you will regret it.

Not the answer you likely want, but it is the answer I suspect you need.

 

[jdraw]

Perhaps you could tell us more of your situation. Why do you have a Duplicates table?
How much experience do you have with Access? To prevent duplicates in a table you would use a primary key -- with that Access will not accept a duplicate.

You might want to read this
http://www.rogersaccesslibrary.com/forum/topic238.html

 

[Harry_Imp]

Thanks guys, have now sorted out the duplicates part.

Is there a way i can add data and i can choose which tables within my databse i want this data to go in?



Thanks
Harry!!

 

[jdraw]

Harry,

You need to do some upfront work before you go much further. Your questions and comments suggest you should do some reading on Normalization and table structure as has been pointed out.
You shouldn't be "choosing" which tables your data is going into. That's what design is about.

This link should help
http://www.rogersaccesslibrary.com/forum/topic238.html

Also look a the models here. They indicate what tables will exist, how they are related, and indirectly what queries will be supported, what reports could be generated etc.
http://www.databaseanswers.org/data_models/index.htm

 

[Harry_Imp]

Ok i will explain,

My database is basically for all the aircraft i have seen. I log the serial, type etc.

One table is one without duplicates, one is with duplicates, so i can see everyherre ive been, and one is for a mate i got aircraft spotting eveywhere with.

So basiclly, i would want to enter a piece of data,and then choose to enter it into my non dupluicates, duplicates, and if my mate had come, his database.

But if my mate doesnt come i would need to be able just to select the other 2 to put it in...


So this is what i want to know how to do?



thanks
Harry

 

[CraigDolphin]

Here's what airplane spotting suggests to me as a likely table structure:

tbl AircraftType
AircraftTypeID (auto, pk)
AircraftTypeName

tblAircraft
AircraftID (auto,pk)
AircraftTypeID (fk)
AircraftSerialNumber

tblLocations
LocationID (auto,pk)
LocationName

tblObservers
ObserverID (auto,pk)
ObserverFirstName
ObserverLastName


tblObservations
ObservationID (auto,pk)
ObservationDate
AircraftID (fk)
ObserverID (fk)
LocationID (fk)


All obervations go into the observations table. Duplicates are essentially individual aircraft that appear more than once in the observations table.

You can query the table to limit results to a particular observer, or a particular aircraft, or a particular location, or a particular time period, or any combination of these that you wish to see.

HTH