Table for Locations over time

[Western_Neil]

Thanks
Neil

[Western_Neil]

Hi All

After having looked at this problem (see table below), I have decided to use current data for location and I might go back and retro fit something in.

One way I see of doing the retrofit is creating the proper tables and using a subroutine to return the data. A second way would be to create a cross with dates, and Boundary table with Place, County, Province, State, and Country move to it. (I know this breaks some rules, but rules are made to be )

Any comments on the above would be welcomed

tpl_Address
ID	Long	Lat	1	2	Place	County	Province	State	Country	Postal Code
1	54°34'46.1"N	1°43'48.3"W	A	101 Front Street	Ingleton	Darlington	County Durham	England	United Kingdom	DL2 3HL
1788						Hesse District	Upper Canada		Great Britain
1791						Hesse District	Upper Canada	British North America	Great Britain
1792						Western District	Upper Canada	British North America	Great Britain
1800					Sand Hills	Western District	Upper Canada	British North America	United Kingdom
1833					Berlin	Western District	Upper Canada	British North America	United Kingdom
1840					Berlin	Western District	Upper Canada	British North America	United Kingdom
1853					Berlin	Waterloo	Upper Canada	British North America	United Kingdom
1867					Berlin	Waterloo	Canada West	Dominion of Canada	United Kingdom
1867					Berlin	Waterloo	Ontario	Dominion of Canada	United Kingdom
1916					Kitchener	Waterloo	Ontario	Dominion of Canada	United Kingdom
1967					Kitchener	Waterloo	Ontario	-	Canada
Today					Kitchener	Waterloo	Ontario		Canada

Notes:
1) Record 1 shows take all fields can be used
2) Next 12 records numbers are the year that the red item changed value.

This is taking the location thing to an extreme, but you never how twisted the data get.

I will close this thread tomorrow PM

Neil

[nrgins]

Are these records all for the same location or different locations? Seems you're saying it's same location, but how things change over time.

If that's the case, then here's what I would do.

Put the things that don't change in one table called Locations. Have ID (autonumber), Long, Lat, and whatever else doesn't change. Maybe notes or description, etc.

Then have second table for things that do change (place, county, province, etc.). Have ID (autonumber), LocationID (long integer; foreign key to Location PK), Year (values currently in ID field), etc.

This way, each table would have a purpose, instead of having blank fields for items that don't change.

Also, don't use "Today" for the year field. Keep the data consistent. Put the year that the info changed to the current values in there. (Can always find the current values by getting the Max value for Year for a location).

One way I see of doing the retrofit is creating the proper tables and using a subroutine to return the data.

I don't understand what you're saying there. Perhaps if you gave some examples of the data you're trying to retrofit, it would be easier to give advice as to how to work with it.

But looks good so far! Needs a little smoothing and polishing, but you're making progress! :-)

[Western_Neil]

Are these records all for the same location or different locations? Seems you're saying it's same location, but how things change over time.

All the year records are the same location. Record 1 is to show that that the fields could be used (bloody English). Today was just a place holder while I was thinking about it. It will be the record enteted now till whenever I do the retrofit.

If that's the case, then here's what I would do.

Put the things that don't change in one table called Locations. Have ID (autonumber), Long, Lat, and whatever else doesn't change. Maybe notes or description, etc.

Then have second table for things that do change (place, county, province, etc.). Have ID (autonumber), LocationID (long integer; foreign key to Location PK), Year (values currently in ID field), etc.

This way, each table would have a purpose, instead of having blank fields for items that don't change.

Yes, I think that what I was hinting at with the Boundary Table. All Man defined and political items in one table (break the rules). I getting confused again of how that will work .... Oh well it future stuff so I can Forget about it !!

But looks good so far! Needs a little smoothing and polishing, but you're making progress! :-)

Thanks Neil
Neil

[nrgins]

All Man defined and political items in one table (break the rules).

Why would that be breaking the rules? Not following.

[Western_Neil]

What I'm taking about is rolling them all into 1 table and using a compound primary key giving
tblBoundary BdyID BdyType BdyTitle

ie.: City Boundary = L & BdyID, County Boundary = C & BdyID, Provincial Boundary = P & BdyID, Sate Boundary =S & BdyID, National Boundary = N & BdyID, Regional Boundary = R & BdyID, Zone Boundary = Z & BdyID, etc..

Neil

[orange]

That would not be a first choice for me. I'd like to see your data model and some test data and scenarios--test the model.
After some testing it may be what you need, but (I'm not up to speed with your BdyID) I would think another field
eg BoundaryType
would seem to work.

[nrgins]

What I'm taking about is rolling them all into 1 table and using a compound primary key giving
tblBoundary BdyID BdyType BdyTitle

ie.: City Boundary = L & BdyID, County Boundary = C & BdyID, Provincial Boundary = P & BdyID, Sate Boundary =S & BdyID, National Boundary = N & BdyID, Regional Boundary = R & BdyID, Zone Boundary = Z & BdyID, etc..

I have to admit I'm pretty lost here as to what you're talking about and what C and L and P, etc. are. But like orange said, we'd need to see some data.

If you're talking about concatenating values into single fields, then I'd advise against that. More than just "breaking the rules," it'll make your data hard work with when it comes time to write queries and reports, filter data, etc. Rules may have been "made to be broken"; but they're also "made for a reason."

[Western_Neil]

I can mock that up if want it. It is future stuff but while you are interested I can learn from a master

[nrgins]

No, it's fine. I'm just saying that we can't really give feedback without seeing clearly what you're talking about. I have no pressing desire to see the data. :-)

If you do post it, please post it as a table or as images, as I hate having to download a db and look through it. I'd rather just see it in the post or see screenshots of what you're talking about. (The way you presented the data before was great!)

[orange]

Your project is much like building a shopping center or new housing development.

You need to identify what's in and what's out --generally.
Next is an artists concept --- it could be something like this. As with most shopping centers or housing developments you see all kinds of "possible park/green areas", but that's marketing. They never show you the sewer lines or electrical cabling (infrastructure).
As you get more facts and place them within your evolving model, you will start to see some "sequence of things that must be done". You can't put in the escalators and window trim before you dig the foundation.....

Bottom line is you can build a model based on some business facts- even a conceptual model- and leave some areas as black boxes--but you know where they fit in the grand scheme, and you know something about what it is(its purpose).

Just some thoughts for consideration.

Take a quick look at this for ideas.

[Western_Neil]

Oh I agree, but I have been putting my toe in MS Access lake for over 20 years and keep getingt scared. I just have to push on and try. Keeping a open mind and trying your guys suggestions does help.
I'm sorry for the attitude.

Neil