Help with a calculated table field

[Bham45]

Sorry--family issues interrupting. Now that I've got my head back on straight (hopefully), it's the Donations by Donor form that I most want to translate into a letter. Is that where we are? Or should this be discussed in the other thread I started "Generating thank you's"?

 

[GinaWhipp]

Nope, not up to the Thank You letters yet, just wanted to see the Form where you are adding the letter.

What Form are you using to enter AddressAs and FileAs or are you going to be doing this directly in the Table?

 

[Bham45]

I have to admit I've lost the train of thought...guess I should re-read this thread from the beginning. But I created the "AddressAs" field to add to the "Thank You Letter Query". I think that's where I decided I didn't like the field "Donor" which is first name first and would rather have FileAs for the way to order names.

Oh...I now remember that it was in the other thread that I told you I didn't realize you could do a mail merge from anything other than a query. So once you instructed me to, I created the Donations by Donors form. I don't think I added the AddressAs field to that form--should I do that now? Or are you saying those fields can be combined in another way?

I'm sorry if I'm being slow today...

 

[GinaWhipp]

Oh, okay then I'm not going to worry about that field for now.

 

[GinaWhipp]

I cannot find where you put...

[Title] & " " & [FirstName] & " " & [LastName]

Where ever you placed it, change it to...

([Title] + ", ") & [FirstName] & (" " + [MiddleInitial])

WHich is what I started out to do! And now that I know which Form you are suing we can mover to the other thread. I'm going to post a question there.

 

[Bham45]

So where do we go now? Should I switch over to the other thread and continue with the mail merge attempt?

Edit: posted this at the same time your post above appeared. So I'll make that adjustment and then switch to the other thread...

2ND EDIT: Wait a sec--should that "MiddleInitial" be "LastName"? I had no MiddleInitial field (though I may add one), and there's no LastName in this expression. And what's the reason for the comma?

 

[GinaWhipp]

Oops, posted wrong one, that should be...

[Title] & (" " + [FirstName]) & (" " + [LastName])

...fresh coffee almost ready and obviously I need some!

 

[Bham45]

Ah...there we go. I just entered a new fake donor with no title and the name is as it should be. Thanks!

I just poured some fresh iced tea...cheers!

 

[GinaWhipp]

No problem. I posted a couple of questions to the other thread, so whenever you get to those...

 

[Bham45]

Hi Gina,

I'm working on the db again, trying to see if I can create a report that looks like a letter--but I"m STILL stuck trying to get rid of that calculated "DonorName" field from the Donor table.

In the Donations table there's the field "DonorID" that's based on the DonorName field from the Donor table. Cannot figure out for the life of me how to replace that DonorID field with "AddressAs" and/or "FileAs" from the Donor table.

There's really NO circumstance that I would want first name first as Donor Name is throughout this db. I would want everything sorted by last name--and would address people by their titles.

Can you help?

 

[GinaWhipp]

On conference call... if no one replies will reply later.

 

[GinaWhipp]

I do not see how the DonorName is based on anything but itself in the Donor table. Remember the DonorName is a Calculated field in the table. Now, if you want to change that display you need to go to the Donor table and on the General tab for the DonorName field you will see the calculation.

 

[Bham45]

It's not DonorName I'm trying to change (I actually want to delete that field once I'm done with the following).

From my previous post:

In the Donations table there's the field DonorID that's based on the DonorName field from the Donor table. Cannot figure out for the life of me how to replace that DonorID field with "AddressAs" and/or "FileAs" from the Donor table.

Understand what I'm saying? Instead of a DonorID field in Donations, I'd rather bring over the fields AddressAs or FileAs.

BUT perhaps this isn't necessary at all? Can the FileAs/AddressAs fields be brought over through a query instead of in a table?

 

[GinaWhipp]

Okay, was trying to answer while on my conference call, my bad!

Open the Donations table and look on the Lookup tab for the DonorID field, that is where the statement that pulls that information is. Just adjust that. (This is just one ore reason I hate doing this at the table level.)

 

[Bham45]

But that's exactly what I"m trying to explain that I've been unable to do.

This is what Row Source says:

SELECT [Donors].[ID], [Donors].[DonorName] FROM Donors ORDER BY [DonorName];

I just now tried (again, for the 20th time):

SELECT [Donors].[ID], [Donors].[FileAs] FROM Donors ORDER BY [FileAs];

--but what I get in the Donor field are numbers: 3, 8, 5, etc.

If this isn't good to do in the table, then PLEASE tell me how to do this outside of a table.

 

[GinaWhipp]

Yeah, I have that table stuff... Better to remove from table and make a Query named... qryDonors. You can actually use the SELECT statement from above to create it. Do you know how?

 

[Bham45]

No. I assume I insert a column, but what/how do I name it and where do I put the statement?

 

[GinaWhipp]

Okay, Copy the select statement from above. Then in Access, open a query using the Query Design on the Create tab. Select Cancel when it asks to select a Table. Select SQL View from the upper left hand corner then paste the SELECT statement and close and save as qryDonors. Double click to open and make sure it's what you want.

 

[Bham45]

Yay! Something finally worked for me! Thanks so much. I created two queries: one for "FileAs" (Smith, John) and "AddressAs" (Mr. & Mrs. John Smith).

BUT this brings up some other questions.

1. I used this statement: SELECT [Donors].[ID], [Donors].[FileAs] FROM Donors ORDER BY [FileAs] --why is [ID] in the statement? What purpose does it serve? The original designer used the statement to create a calculated field he called DonorID--does that have anything to do with [ID] being in the statement? If so, it seems I wouldn't need it...

2. (The more important question) In the Donations table, if I want to now delete the "Donor Name" field (DonorID in design view), what would I do then to link each donations to the correct donor? Do I create a relationship using one of those queries I just created? I'm afraid relationships are not my strong suit.

 

[GinaWhipp]

ID is the Primary Key and is unique to each record. That ID makes it easy to relate to the Foreign Key in other tables or queries or reports so we make sure we are pulling the correct Donor to match. Because using the name presents issues as there can be muultiple people with the same name.

Hmm, let's say my ID is 1. Now, where ever that 1 appears I can easily relate to the query of Donors and pull up my name by linking to the query. Make sense?