Help with SELECT statement?

[azu_daioh]

I dont really know how to phrase this but this is what I have:

TABLE (fields)
FINDINGS (DIFO_Num, Manager, Regional Manager) **DIFO is the office
number
STAFF INFO (DIFO_Num, Name, Title, Remove) **remove is yes/no

In the FINDINGS form, the (manager) & (regional manager) is set as a
list box with the following ROW SOURCE

SELECT [Staff Info].Name
FROM [Staff Info]
WHERE ((([Staff Info].DIFO_Num)=Findings!DIFO_Num)
And (([Staff Info].Title)="Field Office Manager")
And (([Staff Info].Remove)=No));


The reason for the (remove) field is to only use the most current
(manager) for the new records and keeping the older records with the
previous (manager)

For example: Record#1 was created and at that time the manager is XYZ
By the time Record#100 is created, a new manager is ABC

I want to be able to maintain records 1-99 with the XYZ manager and
new records to automatically select ABC as the manager.

However, with the SELECT statement above -- it changes the older
records (manager) to ABC.

Basically, if the current record is for DIFO_NUM "123", the (manager)
and (regional manager) should be automatically completed based on
STAFF INFO table.

Please please help!!! Thank you.

 

[Smartin]

I dont really know how to phrase this but this is what I have:

TABLE (fields)
FINDINGS (DIFO_Num, Manager, Regional Manager) **DIFO is the office
number
STAFF INFO (DIFO_Num, Name, Title, Remove) **remove is yes/no

In the FINDINGS form, the (manager) & (regional manager) is set as a
list box with the following ROW SOURCE

SELECT [Staff Info].Name
FROM [Staff Info]
WHERE ((([Staff Info].DIFO_Num)=Findings!DIFO_Num)
And (([Staff Info].Title)="Field Office Manager")
And (([Staff Info].Remove)=No));


The reason for the (remove) field is to only use the most current
(manager) for the new records and keeping the older records with the
previous (manager)

For example: Record#1 was created and at that time the manager is XYZ
By the time Record#100 is created, a new manager is ABC

I want to be able to maintain records 1-99 with the XYZ manager and
new records to automatically select ABC as the manager.

However, with the SELECT statement above -- it changes the older
records (manager) to ABC.

Basically, if the current record is for DIFO_NUM "123", the (manager)
and (regional manager) should be automatically completed based on
STAFF INFO table.

Please please help!!! Thank you.

Hello,

I strongly suggest you research "database normalization". You are trying
to store too much information in too few tables. That is not to say you
are exceeding table limits of any kind, but rather the information you
want to keep ("historical" being the key word) is not modeled correctly.

E.g., you have one DIFO_Num linked to one Manager. You have stated this
is not how your real-life situation works, where a DIFO_Num might have
several different Managers as time goes on. To model this correctly you
need a separate table of managers, and a "joining" table that keeps
track of when a particular Manager was responsible for a particular
DIFO_Num.

HTH

 

[azu_daioh]

Hi HTH,

I think I got the idea of what you're trying to say. I'm going to try
this.

Thank you.

 

[azu_daioh]

Hi HTH,

I think I got the idea of what you're trying to say. I'm going to try
this.

Thank you.

Hi Again.

Thank you. Your idea helped. Instead of the [remove] field. I
created [start], [end], and [entryDate] fields. Then created a query
to only display the current FO_MGR and REG_MGR based on the start and
end date. I spent so much time with this before and your idea only
took me few minutes.

I'm really grateful my company didnt block google (I can't access
other newsgroups).