By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Access Relationship help please

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. NAME             SSN         DOB     EPR LAST           SUPER
  2. John Doe     111-11-1111  11/2/77     1/1/07         222-22-2222
  3. Jane Doe     222-22-2222   3/4/70      12/4/06       445-22-1111
  4. 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
Feb 26 '07 #1
Share this Question
Share on Google+
19 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. =DLookup("[NAME]","Table or Query Name","SSN=" & [SUPER])
  2.  
Mary
Feb 27 '07 #2

P: 9
Thank you Mary! I'll give it a try.
Feb 27 '07 #3

P: 9
I tried as suggested but to no avail, I had no luck. Any other suggestions?
Feb 28 '07 #4

Rabbit
Expert Mod 10K+
P: 12,334
Expand|Select|Wrap|Line Numbers
  1. =DLookup("[NAME]","Table or Query Name","SSN='" & [SUPER] & "'")
  2.  
Had to modify Mary's code a little. SSN is stored as a text field.
Feb 28 '07 #5

NeoPa
Expert Mod 15k+
P: 31,299
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?
Feb 28 '07 #6

NeoPa
Expert Mod 15k+
P: 31,299
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).
Feb 28 '07 #7

NeoPa
Expert Mod 15k+
P: 31,299
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.NAME,T1.SSN,T1.DOB,T1.[EPR LAST],
  2.        T1.SUPER,T2.Name AS SUPERNAME
  3. FROM YourTable AS T1 LEFT JOIN YourTable AS T2
  4.   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.
Feb 28 '07 #8

P: 9
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
Mar 1 '07 #9

NeoPa
Expert Mod 15k+
P: 31,299
Main Data Query is the source
And ... ?
Mar 1 '07 #10

ADezii
Expert 5K+
P: 8,615
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:

Expand|Select|Wrap|Line Numbers
  1. NAME             SSN         DOB     EPR LAST           SUPER
  2. John Doe     111-11-1111  11/2/77     1/1/07         222-22-2222
  3. Jane Doe     222-22-2222   3/4/70      12/4/06       445-22-1111
  4. 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.
Mar 1 '07 #11

P: 9
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.
Mar 1 '07 #12

NeoPa
Expert Mod 15k+
P: 31,299
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.
Mar 1 '07 #13

NeoPa
Expert Mod 15k+
P: 31,299
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.
Mar 1 '07 #14

NeoPa
Expert Mod 15k+
P: 31,299
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.
Mar 1 '07 #15

P: 9
Expand|Select|Wrap|Line Numbers
  1. SELECT [Main Data].SSAN,
  2.        [Main Data].RANK,
  3.        [Main Data].SUPER,
  4.        [Main Data].NAME,
  5.        [Main Data].[OFF SYM],
  6.        [Main Data].[Type Award],
  7.        [Main Data].[Date DECOR Received],
  8.        [Main Data].[Date DECOR to 9RW],
  9.        [Main Data].[Date DECOR RTD for Correction],
  10.        [Main Data].[Date DECOR to MPF],
  11.        [Main Data].[Date RIP DECOR  received and sent],
  12.        [Main Data].[Date DECOR due to SGS],
  13.        [Main Data].[DECOR Susp to MPF]
  14. FROM [Main Data] INNER JOIN [Main Data] AS [Main Data_1]
  15.   ON [Main Data].SUPER = [Main Data_1].SSAN
  16. WHERE ((([Main Data].SUPER)=
  17.          DLookUp("[NAME]",
  18.                  "Main Data",
  19.                  "SSAN='" & [SUPER] & "'")
The original post is from Main Data
Mar 1 '07 #16

NeoPa
Expert Mod 15k+
P: 31,299
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).
Mar 1 '07 #17

NeoPa
Expert Mod 15k+
P: 31,299
...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 :
Expand|Select|Wrap|Line Numbers
  1. [Main Data_1].NAME AS SuperName,
Leaving SQL for your [Main Data Query] of :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Main Data].SSAN,
  2.        [Main Data].RANK,
  3.        [Main Data].SUPER,
  4.        [Main Data].NAME,
  5.        [Main Data_1].NAME AS SuperName,
  6.        [Main Data].[OFF SYM],
  7.        [Main Data].[Type Award],
  8.        [Main Data].[Date DECOR Received],
  9.        [Main Data].[Date DECOR to 9RW],
  10.        [Main Data].[Date DECOR RTD for Correction],
  11.        [Main Data].[Date DECOR to MPF],
  12.        [Main Data].[Date RIP DECOR  received and sent],
  13.        [Main Data].[Date DECOR due to SGS],
  14.        [Main Data].[DECOR Susp to MPF]
  15. FROM [Main Data] INNER JOIN [Main Data] AS [Main Data_1]
  16.   ON [Main Data].SUPER = [Main Data_1].SSAN
Mar 1 '07 #18

P: 9
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?
Mar 2 '07 #19

NeoPa
Expert Mod 15k+
P: 31,299
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.
Mar 2 '07 #20

Post your reply

Sign in to post your reply or Sign up for a free account.