Pulling snippets of information from a master DB

[MWalt170]

Greetings,
I am very new to access, no formal education, and unfortunately am the 'smartest' when it comes to databases of my immediately peer group.
"Irregardlessly"...

In order to make my intent clear, I'm going to provide more information that may be necessary.
I'm in the National Guard. All Soldiers conduct a two week annual training (AT) period every year. Soldiers can go to the four winds for their AT period, not only that, where they go can change. Therefore I want a list on our SharePoint site that includes some information about the Soldier and where they're going for AT.

There is a database populated from the netherworld (I have no idea where all this information comes from) called SIDPERS. This DB is updated nightly.
In this database are Soldier records, name, social security number, address, rank, gender, and unit of assignment.
In this database are thousands of Soldier records. I want to create a query that looks at SIDPERS for only Soldiers assigned to x,y, and z units then populate my list with only those Soldiers.
This reduces the number of Soldiers I have to worry about from thousands to hundreds.

Then I know in SharePoint my co-workers, each responsible for a fraction of those hundreds, can update an independent column with information about where those Soldiers are going for their AT.

However, Soldier moves happen often, so I want to be able to rerun the query that looks at SIDPERS. If one of my hundred some Soldiers is moved to another unit outside my organization, I want them deleted from my SharePoint list. If a new Soldier is added to my organization, I want them to appear, additionally if a Soldiers rank, address, gender, etc. changes I want that information to update also. All the while keeping the information that my co-workers entered for their individual Soldiers.

Make sense?



Here's what I have so far. Links connected to their appropriate categories. The Soldiers assigned unit is represented by a unique 5 digit code called a UPC (V7QA0 is Alpha Company). Grade is the Soldiers rank e.g. Sergeant, Lieutenant, Captain, etc.
So if I run this update, all the information should pull from SIDPERS and update my list on SharePoint (TY17AT Projections), correct?
Then do I just create an append query and a delete query to add/remove records that are/aren't in the desired UPC?
How do I avoid creating duplicate records with the append query?

Thanks for your time!
-MW

[Villeroy]

This forum is about the free office suites OpenOffice and LibreOffice. Hardly anybody here can help with Microsoft products.

I am very new to access, no formal education, ...

This might be the root of the problem as indicated by your relations graph. MS Access makes it very easy to generate some database that works somehow. However, database design is development work. Development work requires a certain level of formal education.

[keme]

If my memory serves me right, Access has the SQL view available.
In short:
To get data from a database, use a SELECT query. This pulls data from the source, so you don't need to actively delete in your copy what has been removed in the source. It's already gone...

Code: Select all

SELECT * FROM SIDPERS;

To extract only those in relevant units, you can keep your own table listing the units you have. Join that to the source to get relevant info.

Code: Select all

SELECT * FROM MyUnits LEFT JOIN SIDPERS ON SIDPERS.curr_upc = MyUnits.ID;

If you need to change data, you use an UPDATE query. Make sure you have your "where" clause right, or you may update the entire table. (Run it as a SELECT query first so you just see what records would have been updated.)

Code: Select all

UPDATE SIDPERS
SET WherAreTheyGoing = WhateverHasBeenInput
WHERE ssn_sm = ThePersonWeAreUpdating;

So, you just work on your SIDPERS database all the time. From your comment about "updated nightly" I suspect that your SIDPERS is a "shadow copy". Whether your changes are lost or refreshed with nightly updates depends on the maintenance strategy for that copy. Talk to the people managing the personnel DB. (Yes, you may have to venture into the netherworld.)