I'm a fool for your querying

[bpaquette]

okay, so i hvae two tables, we'll call them "a" and "b".

A has a series of names.

B has a series of what we call "position numbers". For resource mgt purposes, we have to slot each individual into a position #. Now two people can belong to one position #, but one person cannot be in two position #'s, and position #'s can be blank.

What i need to do is generate a query that shows all the position #'s and all the names that are in those #'s. If three people are in slot #5, then i need to show three separate records for slot #5, and if no one is in slot #35, then that has to show up with no one in it.

Now, the reciprocal is easy -- showing every person and their position # w/ a simple relationship , so I assumed this way would be easy too -- but it's not!!

Am I a fool, is this easy? Or is it impossible? Hrm.

And now the hard part -- which icon to attach to this post...

 

[ecniv]

okay, so i have two tables, we'll call them "a" and "b".

A has a series of names.

B has a series of what we call "position numbers". For resource mgt purposes, we have to slot each individual into a position #. Now two people can belong to one position #, but one person cannot be in two position #'s, and position #'s can be blank.

What i need to do is generate a query that shows all the position #'s and all the names that are in those #'s. If three people are in slot #5, then i need to show three separate records for slot #5, and if no one is in slot #35, then that has to show up with no one in it.

Now, the reciprocal is easy -- showing every person and their position # w/ a simple relationship , so I assumed this way would be easy too -- but it's not!!

Am I a fool, is this easy? Or is it impossible? Hrm.

And now the hard part -- which icon to attach to this post...

tblEmployees
EmployeeID - auto (PK)
Forename - text
Surname - text
PositionID - number (FK)

tblPositions
PositionID - auto (PK)
PNo - (text or number)
PTitle - text


Then a query:

Select tblPositions.PNo, tblPositions.Ptitle, nz(tblEmployees.Forename,"") as EForename, nz(tblEmployees.surname,"") as ESurname
From tblPositions left join tblEmployees on tblPositions.PositionID=tblEmployees.PositionID
Order by tblPositions.PNo

Vince

 

[bpaquette]

Thanks! However, i'm a gui kind of guy, so that code would be impossible for me to implemenet into my already existing tables without learning some of the syntax and how it translates into gui actions -- mind briefing me? such as nz and that sort of thing.


thanks
bp

 

[ecniv]

tblEmployees
EmployeeID - auto (PK)
Forename - text
Surname - text
PositionID - number (FK)

tblPositions
PositionID - auto (PK)
PNo - (text or number)
PTitle - text


Then a query:

Select tblPositions.PNo, tblPositions.Ptitle, nz(tblEmployees.Forename,"") as EForename, nz(tblEmployees.surname,"") as ESurname
From tblPositions left join tblEmployees on tblPositions.PositionID=tblEmployees.PositionID
Order by tblPositions.PNo

NZ = function that if the value is null it is replaced with the specified default.
(see help files)

The above is the Sql statement, if you copy the table layouts, create a query, close the adding tables window, change the view to sql (from builder/dataview) and paste the sql statement in then change back to builder view it should have the tables in a gui format.

Attached is a piccy of the query builder layout made in ms paint, transfered to ms photodraw v2 (both crap)...


Vince

 

[Sergeant]

I hate to oversimplify, but...

bp,
It sounds like you already know what you need to do. You just need to change the relationship line in the query builder. Open your query in design view, right click on the line between posn # in the personnel table and posn # in the positions table and click join properties, then select "include all records from table positions and only those records from table personnel..."

You will, however, miss out anyone who does not have a valid posn # in the personnel table. There are other ways of getting these.

I do this same thing to build personnel reports. All positions are listed, whether they have someone sitting in the position or not. I have a group header on posn #, so each posn # is printed on the report only once, and under it are all the people in that posn #. I then use another query to find the people who are not loaded in valid posn #'s and it drives a sub-report attached to the bottom of my report. This is like an errors section, telling you who needs to be placed in a valid posn #.

Take Care,
Sarge.

 

[raskew]

You haven't stated whether there is any interest in maintaining
a history of who occupied what position when.

The previous guidance will work fine, but when someone moves from
position A to position B, you lose any record that the individual ever
occupied position A. When, X months from now the boss comes and
says '...we're considering Employee 42 for promotion to ????, please
provide a history of his/her service with the organization...' you will find
yourself in deep doo-doo and will probably be forced to manually
screen the personnel file to extract the information.

Since you are in the design stage, consider adding a third table to link
tblEmployees and tblPositions. It would look something like:

tblEmployeesAndSlots
- SlotID (PK / Autonumber)
- EmployeeID (FK / Long)
- PositionID (FK / Long)
- StartDte (Date/Time)
- EndDte (Date/Time)

...and would be linked between tblEmployees and tblPositions. It's
a little work now that could save a lot of work somewhere down the
the line (I know this from experience, having taken the quick way only
to have to rebuild my application months later to meet new management
requirements).

Best wishes - Bob

 

[bpaquette]

aye, there's the rub

I appreciate your suggestions, guys.

Bob -- we fortunately have other software to maintain personnel data, so I don't have to maintain a precise history.

Sgt - thanks for your pointers; that sounds exactly like what i'm trying to do (you sound like you work for the same bosses i do!). my query is currently configured exactly how you have it (show all posn #'s from UMD table, and only matching records from personnel), but it's still only showing filled posn #'s. I know I have blank #'s, but they just won't show.

Could there be something wrong elsewhere?

 

[Sergeant]

Are you showing any fields from the positions table? When you switch the query to datasheet view, you don't have any blanks under the (name) column? (p.s. don't name the name column "name")
Do you have any criteria in the query that might prevent blanks from showing?
Maybe you should make a new query. ??

It's after 16:00 here, and my helpfulness rating drops sharply at about 15:59, only to increase again around beer:30 over the laptop.
Feel free to PM me.

Take Care,
Sarge.

 

[ecniv]

Post up your current sql statement. It might (just might) shed some light


Vince

 

[bpaquette]

Sgt -- I'm assuming you're assigned to Yokota? I'm at Ramstein myself!


Anywho, I finally figured out what was wrong -- thanks to whoever suggested examining criteria that would eliminate any records with blank fields -- I can't believe I missed that!

THanks to everyone who took time to assist me on this matter.


bp

 

[Sergeant]

I'm actually at Kadena.

Glad you found your problem with the query. Sometimes we are just too close to the thing to realize.
I assume you are making an UMPR (Personnel/Position roster)? I have a pretty good one, but I still have some improvements to make. I made it accessible to all the Chiefs in my Group, and I also provided a personnel data lookup form that limits records to personnel in their organization. (I use the WINNT username to decipher who it is, and check them in the User table). The whole thing is updated via FOCUS reports from PC-3 (AF Personnel computer).

Sarge.