Thanks
Neil
Thanks
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
Last edited by Western_Neil; 07-12-2016 at 11:59 PM.
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).
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.One way I see of doing the retrofit is creating the proper tables and using a subroutine to return the data.
But looks good so far! Needs a little smoothing and polishing, but you're making progress! :-)
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.
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 !!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.
Thanks NeilBut looks good so far! Needs a little smoothing and polishing, but you're making progress! :-)
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
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.
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.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..
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."
I can mock that up if want it. It is future stuff but while you are interested I can learn from a master
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!)
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.
Last edited by orange; 07-21-2016 at 01:50 PM. Reason: original [b]had[/b] can not can't
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
Last edited by Western_Neil; 07-14-2016 at 07:15 AM. Reason: Bad rant