Access Relationship help please

[MooseManDan]

This is a very nice site and I'd like to say thank you to the mdoerators and site administrator for maintaining. This is my first post; I hope someone can assist.

I'm working on a database for my wife. I pull reports from a program that defines the headings: SSN, NAME, DOB, PR EPR, Last EPR, SUPER, etc..

The supervisor = SUPER and the SSN = their Social Security Number

When I pull the data, the Super comes out with a Social Security Number. I need to establish a relationship or query that identifies the SSN by their NAME.

I can only download the main data in a set manner, there are no adjustments to the linked table in my database.

Example:

NAME             SSN         DOB     EPR LAST           SUPER
John Doe     111-11-1111  11/2/77     1/1/07         222-22-2222
Jane Doe     222-22-2222   3/4/70      12/4/06       445-22-1111
Jack Doe     333-11-2442   6/1/81      11/3/05       222-22-2222

In this illustration, you can see that Jane supervises John and Jack. 1) How do I get her name to come up in a query and all the personnel she supervises? 2) In a report, when I have SUPER selected, how can I get it to show the NAme instead of the social security number?

Thanks for the help!

MooseMan

[MMcCarthy]

Hi MooseMan

Hide the SUPER field on the report but don't remove it. Now add a New text box and set the control source to

=DLookup("[NAME]","Table or Query Name","SSN=" & [SUPER])
 

Mary

[MooseManDan]

Thank you Mary! I'll give it a try.

[MooseManDan]

I tried as suggested but to no avail, I had no luck. Any other suggestions?

[Rabbit]

=DLookup("[NAME]","Table or Query Name","SSN='" & [SUPER] & "'")
 

Had to modify Mary's code a little. SSN is stored as a text field.

[NeoPa]

The recommended approach would be to link the table to itself in the source query (that would be another instance of itself). A LEFT JOIN would be required unless you could state categorically that all records have a SUPER value.
What do you currently have as the source of your form?

[NeoPa]

BTW the other Experts here who posted the DLookup() solutions are both sacked (even though one outranks me of course). She should know better (tsk-tsk).

[NeoPa]

SELECT T1.NAME,T1.SSN,T1.DOB,T1.[EPR LAST],
       T1.SUPER,T2.Name AS SUPERNAME
FROM YourTable AS T1 LEFT JOIN YourTable AS T2
  ON T1.SUPER=T2.SSN

Is the sort of thing you need. I don't know your table name so I improvised but I'm sure you get the idea. Using DLookup is actually something you can get away with in most cases, it just gets slower very quickly as the number of records to process increases (They know I'm just kidding). Not recommended as good design though.

[MooseManDan]

The recommended approach would be to link the table to itself in the source query (that would be another instance of itself). A LEFT JOIN would be required unless you could state categorically that all records have a SUPER value.
What do you currently have as the source of your form?

Main Data Query is the source

[NeoPa]

Main Data Query is the source

And ... ?

[ADezii]

This is a very nice site and I'd like to say thank you to the mdoerators and site administrator for maintaining. This is my first post; I hope someone can assist.

I'm working on a database for my wife. I pull reports from a program that defines the headings: SSN, NAME, DOB, PR EPR, Last EPR, SUPER, etc..

The supervisor = SUPER and the SSN = their Social Security Number

When I pull the data, the Super comes out with a Social Security Number. I need to establish a relationship or query that identifies the SSN by their NAME.

I can only download the main data in a set manner, there are no adjustments to the linked table in my database.

Example:

NAME             SSN         DOB     EPR LAST           SUPER
John Doe     111-11-1111  11/2/77     1/1/07         222-22-2222
Jane Doe     222-22-2222   3/4/70      12/4/06       445-22-1111
Jack Doe     333-11-2442   6/1/81      11/3/05       222-22-2222

In this illustration, you can see that Jane supervises John and Jack. 1) How do I get her name to come up in a query and all the personnel she supervises? 2) In a report, when I have SUPER selected, how can I get it to show the NAme instead of the social security number?

Thanks for the help!

MooseMan

The easiest solution would be a variation of an Auto Join. Create a Query, then enter your Table in it twice. If your Table is named Table1, then entering it into the Query Grid again would produce Table1_2. Create a Join between the SSN Field of Table1 to the Super Field of Table1_2 (Table1.[SSN] ==> Table1_2.[SUPER]. Add any other Fields as required. Make this the Record Source for your Report, then set Hide Duplicates to Yes on the Name Field if so desired.

[MooseManDan]

Okay,

I tried as suggested and the code did not list the name in place of the SSN, instead it lists error#.

Here is how the DB is set up...

Tables: DOWNLOAD (Link table) and MAIN DATA. DOWNLOAD is the excel spreadsheet that has all the main information in it. I loaded that into MAIN DATA table so I can add additional fields. I did this because I can only download the DOWNLOAD sheet. How can I set up a feature that appends information to the MAIN DATA sheet when I update the DOWNLOAD linked table?

I have one query set up right now, Main Data Query. It pulls from MAIN DATA.

In the MAIN DATA, SUPER field is formated in this way: Number, 000-00-0000, and NAME field as text, 255

I really appreciate your help on this.

[NeoPa]

Main Data Query is the source

And ... ?

Sorry to sound short with you, but I need to know more precisely what the name is ([Main Data]; [Main Data Query]) and I need to know what more you would like me to help with.

[NeoPa]

Okay,

I tried as suggested and the code did not list the name in place of the SSN, instead it lists error#.

Here is how the DB is set up...

Tables: DOWNLOAD (Link table) and MAIN DATA. DOWNLOAD is the excel spreadsheet that has all the main information in it. I loaded that into MAIN DATA table so I can add additional fields. I did this because I can only download the DOWNLOAD sheet. How can I set up a feature that appends information to the MAIN DATA sheet when I update the DOWNLOAD linked table?

I have one query set up right now, Main Data Query. It pulls from MAIN DATA.

In the MAIN DATA, SUPER field is formated in this way: Number, 000-00-0000, and NAME field as text, 255

I really appreciate your help on this.

I missed this earlier as I had loaded the page a while back.
I try to fit these in at work when I get a short break so I may have a number open for a while just waiting.
I'll read through this now and try to come back to you on it shortly.

[NeoPa]

How can I set up a feature that appends information to the MAIN DATA sheet when I update the DOWNLOAD linked table?

I don't understand this question :(

I have one query set up right now, Main Data Query. It pulls from MAIN DATA.

In the MAIN DATA, SUPER field is formated in this way: Number, 000-00-0000, and NAME field as text, 255

Is the data you showed in the original post from the [Main Data Query] or is it from [MAIN DATA]?
Can you post the SQL of [Main Data Query] so we can understand better what's going on.

[MooseManDan]

SELECT [Main Data].SSAN,
       [Main Data].RANK,
       [Main Data].SUPER,
       [Main Data].NAME,
       [Main Data].[OFF SYM],
       [Main Data].[Type Award],
       [Main Data].[Date DECOR Received],
       [Main Data].[Date DECOR to 9RW],
       [Main Data].[Date DECOR RTD for Correction],
       [Main Data].[Date DECOR to MPF],
       [Main Data].[Date RIP DECOR  received and sent],
       [Main Data].[Date DECOR due to SGS],
       [Main Data].[DECOR Susp to MPF]
FROM [Main Data] INNER JOIN [Main Data] AS [Main Data_1]
  ON [Main Data].SUPER = [Main Data_1].SSAN
WHERE ((([Main Data].SUPER)=
         DLookUp("[NAME]",
                 "Main Data",
                 "SSAN='" & [SUPER] & "'")

The original post is from Main Data

[NeoPa]

I'm not sure exactly where we are atm but I'm sure the SQL you've posted doesn't work (After displaying it visibly).
Firstly, the parentheses in your WHERE clause are not balanced.
Sendly, the whole WHERE clause is nonsense (sorry to be stark about it).

The good news is that you can just drop it completely. The SQL up to that point is perfect (As far as I know from what I do know of your data).

[NeoPa]

...Except that while you've linked perfectly to the matching record of the SUPER you haven't actually selected any field from that record to show.
An extra line should do the trick, in the SELECT clause :

[Main Data_1].NAME AS SuperName,

Leaving SQL for your [Main Data Query] of :

SELECT [Main Data].SSAN,
       [Main Data].RANK,
       [Main Data].SUPER,
       [Main Data].NAME,
       [Main Data_1].NAME AS SuperName,
       [Main Data].[OFF SYM],
       [Main Data].[Type Award],
       [Main Data].[Date DECOR Received],
       [Main Data].[Date DECOR to 9RW],
       [Main Data].[Date DECOR RTD for Correction],
       [Main Data].[Date DECOR to MPF],
       [Main Data].[Date RIP DECOR  received and sent],
       [Main Data].[Date DECOR due to SGS],
       [Main Data].[DECOR Susp to MPF]
FROM [Main Data] INNER JOIN [Main Data] AS [Main Data_1]
  ON [Main Data].SUPER = [Main Data_1].SSAN

[MooseManDan]

That worked perfectly; thank you very much.

On another note: How can I set MAIN DATA Table to update automatically with the DOWNLOAD (linked excel sheet) table?

[NeoPa]

Not a problem. I'm pleased that helped.

On the other note, You should post this as a new thread as it's not really connected with the question asked in this one.
I expect you'll need to phrase it more clearly though, explaining exactly what you need help with.