Update query for missing data?

[Homegrownandy]

I have a list of sites on my database. A site is just a name for a geographical location. Often on the database a site may ONLY have the name. Meaning I must get the address and whatever else in the future.

I have a few questions surrounding this but no actual end goal as of yet.

*If I had an excel document (set up the same way as the fields of course) Could I somehow have that add sites that don't exist, but also update missing information? If so how would I go about that?

*also.. the site name is not a unique field the site_ID is. But I would like to know when there are repeat entries. Is this easily done?



I might have more questions or ideas when I think about this more and hopefully have some replies here to look at.

Cheers, Andy

[June7]

Not sure what you mean by 'add sites that don't exist' - how would Excel help add sites that don't exist? Data must be entered somewhere. Do you mean there are sites on the Excel sheet that have not been input to the table in Access?

Access can link to Excel worksheet that has a simple structure. That linked dataset can be used like a table - except cannot edit data.

An aggregate query can return a count of duplicate data.

[orange]

Andy,

As with any project, it is a good practice to scope out the big picture view and develop a plan based on priorities.
I suggest you get a description of WHAT you are trying to accomplish. If you understand clearly WHAT you are trying to do, you can create your entities and relationship in rough form with pencil and paper. Create some dummy data and test the model. If you build tables correctly, you can always add records to those tables.

But we don't know what you are dealing with, so some sample data and brief description could/would help readers greatly.
Good luck with your project.

[Homegrownandy]

Yes sorry June, I meant sites that are not in the database. I receive an excel document which is structured the same way as my sites table. Currently I just import this data. But a couple of times there has been multiple entries for a site due to our clients not always being aware of what's been sent.

Orange - I have to laugh because I expect you to post that every time I make a thread lol. All my entities and relationships are fine. I do not need help with these. If I ever do I will be sure to describe them as accurately as possible. I just want to know how to manage new data in one table. How that data is used or what it relates to doesn't matter.

I'm seeking answers that can be applied to any database, that's why my questions are structured in such a way. I'm happy to do all of the work myself, unless its VBA haha. Often I know what I want to do I just don't know what that procedure is called. Like June just mentioned an aggregate query. Ill go off and investigate what that is and see if its what I need or if I can apply it. I keep explaining that I'm more interested in the theory rather than having you build me a database. But also, there's always room for improvement in explanations.

I find it funny. But if you disagree with anything let me know what man. Usually I say something similar and you don't post on that thread again.

When I read any tutorial online, they don't describe relationships or business model. They explain how to achieve a certain goal. All I'm doing is defining what that goal is and looking for pointers.

[Homegrownandy]

Anyway, Back to the topic.

If a site does exist in the database, but it only has the "site_name" field. Then in an excel document it exists, but it also has the "site_address" field.
How would you suggest I deal with such events?

This is all within one table.

That's my question, and currently I'm thinking; Just add the list as a whole, don't check for such things. Then do an aggregate query (if that does what I want, I need to look) then if a "site name" does appear twice I can see if the new data is any more descriptive.

So ill just be comparing the duplicate site names to see if any other field has information that was originally missing.

Suggestions appreciated.

[June7]

As I said, Access can link to simple Excel worksheet and that dataset can be used as source to update the table. Whether or not deleting the records from table and pulling in all records from Excel to repopulate the table is a reasonable option depends on your data structure. If the Sites table has an autonumber PK that is saved as a FK in related table, then this is not a good idea.