Query issues

[aytee111]

You are probably going to hate me, I have changed everything! The first thing I have done is redesign your tables. You (and I) are struggling now because they don't make sense. From the little you've told me I hope I got it right:

These are the tables that are static, basically never change, just get added to. They contain the master lists of information required everywhere else.
- table of GPS locations
- table of project names
- table of geographic locations

Dynamic tables.


- table of projects saying which project, which year, which location
- photos - which GPS location, which project/year, 4 photo files

Using this structure you can easily run queries and get whatever data you need. Populating the last table is probably where you are stuck right now, is that right?

[admessing]

I see a few issues though....

1. The project table must contain the project name and project location (they are both required).

2. The Photo File table is missing. This serves as the entire basis for creating the paths to the individual photos, and is there to speed data entry (ie...enter the file base once, and have a make table query mash it all together into a complete package...also saves on issues with fat finger typing).

3. All of the main tables must contain the same Primary Key (Unique_ID) as per database guidelines (Don't ask me why, but that is how they (D.C.) want it done...~ ..)

Other than that....

Where do I go from here? Do the queries need to change (if so...How?)? Any more suggestions?

[admessing]

But, if we can come up with a good reason for there to be different ID fields, they may just relent on that one to make this thing work.

But, in the end, the main tables must be able to be updated via the entry form, with the secondaries getting populated via the queries.

I am coming to the end of my patience with this DB....normally I don't do regular DBs...I usually only deal with geodatabases for purely mapping purposes. But I got saddled with this project since it does contain spatial coordinates (GPS points), and the archive output will be used to build an interactive interface for a map.

[aytee111]

Shoot, what happened to all our posts? I wrote you a whole long story in answer to your concerns, now they are all gone?

I don't know if I can remember, but here goes

1. Project must have location, I think you said. Not so. Normalization of the data says no repeating. My new structure lets you get the project and the year and the location, all thru the new Years table.

2. No photo table? There is a new photo link table which contains all the information you need to get the photos. The GPS information is kept on the Stratial table, one easy link away, and the photo files themselves are stored as hyperlinks which are direct links to the files instead of wads of text pointers.

3. The unique id can go on the Years table if you want to. The project table is a list of names, just the one column, so it really is meaningless. The geographical locations same, just a list of names.

From here, you need to decide if you want to make these changes or not. If you do, first make a copy of the database, then go ahead and make the changes that you think make sense to you. After that we can worry about the forms and reports and queries - they will be much simpler, believe me.

[aytee111]

OK, now I see it all again. It went into a black hole there for a while!

I'm sorry to hear that you are losing patience - and I am probably making it worse telling you to make major changes to it. Databases are fun (most of the time). However, don't feel pressured from me. It will work great when it is finished!

[admessing]

No...you are being a big help. Like I said I don't normally deal with regular DBs. I leave that to the admins....but in this case I got the project handed to me because it will be used for mapping purposes once all the wrinkles are ironed out.

Let me see what I can do with finding your original post with the changes. I think I ran into the black hole too. Will continue with this later today or tomorrow since I have a meeting to go to in a little bit.

[aytee111]

Let me take a day or two to ponder this and come up with a better - simpler - solution.

[admessing]

No problem....got side-tracked today with a last minute request for data on another project, so didn't get time to do anything with it like I planned.

I should be around after 7am MST tomorrow.

[aytee111]

Here is something that may or may not be what you want. I have made it a new database but you can import the objects into your own. Your post says you are using version 2007.

There are two tables - I combined the Project and Photo tables.
One form - I did not make it pretty, I will leave that to you.

Hopefully the form is self-explanatory.

[admessing]

Just tried to take a look at te DB you posted. My system says that it is an Access 2007 format, but I am unable to open it. I get the "unrecognized database format" idiot popup.

{edit} I work mostly with .mdb (Access 2003) formats. You will notice that the sample DB I gave you is a .mdb...this is the default that we use since it works with our mapping software.

[admessing]

Still no luck. The DB posted in the reply blew up when I tried to enter new records to test it. It won't update the records properly, nor will the form populate any fields if using the select box in the forms. Not quite sure where to go from here....I may just start from scratch again and see what I can do with it. Any ideas would be helpful.