Replies

Dave,
I’m going to keep the Yes/No field for Life Memberships, so that I can easily keep track of them. RetiredGeek’s suggestion will let me design a better query and sort the data better. I had to recreate the database last year from someone else’s work, but it was out-of-date. We are still figuring out what names to keep and delete. Once I’ve made the changes, I’ll create a few new queries so that I can print Life members, current members, and need-to-renew members labels separately.

Sue

Hi sueri,
If MembershipRenewDate will be used only as described in your original post, then RetiredGeek’s suggestion seems the most practical.

But, if you changed the database that way and then decided to create a report of all members and their membership renew dates, this would entail “translating” the dummy date of 2525 to “Life”.

In contrast, your present database structure explicitly recognizes the concept Life. This wouldn’t necessarily make our hypothetical report “easier” to construct, but it would make it more intuitive because your present database structure reflects the historical society’s “business rules” more accurately. This would probably also allow the database to meet unanticipated needs more gracefully.

Hope this helps,
Dave

Sueri,

What I’d do is to ditch the Life Member field and change the Renewal Date field to a type of Date then just enter a date of say 12/31/2200 for Lifetime Members, of course if you think your database will survive longer than that make it 2525 In the Year 2525. This will allow you to do date comparisons for those who need to renew (e.g. greater than the last day of the previous month and less than the first day of the next month) while easily avoiding the life members.

HTH :cheers:

Figured I was missing something easy. Thank you.

I’m using Windows 7 without automatic updates. Within the last week, I’ve had two pop-ups show up about Windows 10. My options are to X-out, Upgrade, or OK. That is it. There is no decline, unless OK now means no. I’m in the middle of a big project and don’t want to go through the hassles my husband has with Windows 10. He had 8.1 and hated it. Windows 10 was an improvement, but he is often having problems with programs, and then after restarting a few times, programs will get back to working.

My solution so far is to go to shut-down and close out the computer without touching the pop-up. That is eliminating the pop-up and so far I haven’t had Windows 10 loaded on my computer.

Mark,
The requery does work, but I’m not sure how to requery and close the form. I tried this, but it didn’t work.
Private Sub cmdCloseAddNames_Click()

End Sub

Private Sub Form_Click()

Forms!frmEntries!cboIDNames.Requery

DoCmd.Close acForm, “frmAddNames”, acSaveYes

End Sub

Thanks,

Sue

Thank you. This has answered my question. I’ve saved the tips on how to do this. What I’m trying to do at the moment is get the names into 4 columns connected to the names table, so that I’m connecting names with an entry. Then I can export them to make a linked table with the entry primary key and then the name primary key. It is the only way I’ve figured out how to get them connected. I want a subform where I can have several names linked to one entry greatly reducing errors. The people were typing in each field for each entry just like one would with Excel. The amount of errors generated was truly amazing.

I have places, sources, extractors, and other tables connected to entries, but haven’t connected the biggest table, names, yet. Once that happens, I’ll be back with more questions. Thank you very much.

Sorry, I know better. I’m using Access 2010. It looks like Retired Geek has answered the question.

John,
I just changed the name of the two fields in the query and it works like a champ. Thanks again.

John,
The query comes up with expression 1 and 2. I’ll look through your sample database to see what I need to change. If I can’t figure it out on my own, I’ll post again. I’ll remember better if I work it out and only ask if I can’t.

Thanks again, I really like having the information in the format. It will really improve the project. You can see what we are doing at http://www.crossroadsarchive.org and for the images at http://www.crossroadsarchive.net. We are going to put the report up on the .org site, so I’ll probably post down the roads with more questions. If you would like to donate your time to the project it would be greatly appreciated. The donation form is at the .org site under Documents, Contributor forms. In your case, you would use what you normally charge or $15 US, whichever is higher. Just scan it and email me.

John,
The queries worked. I had to fix a problem with apostrophes as they created an error in the query for some reason. If my txtConcatNames had an apostrophe in it, the query wouldn’t return the item numbers and an error message would show. Those are fixed and the query returns without errors.

There is an error with the report. I’m getting a request for Enter Parameter Value for txtLastName, followed by for txtGivenName. If I step through those, the items numbers come up, but not the names.

Any ideas?

Sue

John,
Thank you. I’ll work with the four new items. I’ll let you know how it works. I very much appreciate you sharing your expertise.

John,
All I need for the report are the txtConcatNames, txtLastName, txtGivenName, and txtConcatItemPg, which are all in tblItemListWNames. I hope to use the txtConcatNames to be the unique to generate in the report but hidden and use txtLastName and txtGivenName for display.

txtConcatNames (Hidden)
txtLastName and txtGivenName txtConcatItemPg

Would look like:
AustinCG (Hidden)
Austin, C. G. N1540P1, N1541P1, N1542P1, N1543P1

For Paul,
We are using Excel to record the names for insertion into the meta data of the images and then uploading into the database, so I do all the merging using an Excel function like =”N”&B2&”P1″ or =”N”&B2&”P”&”E2″ if there is more than one page per item. I tried concatenate in Access, but would have had to add the NotStated or Company to the txtGivenName field and I didn’t want them to show in the actual report.

Sue

John,
Here is a sample of the database. Hopefully this works. Any suggestions on improving it are very welcome. I would rather fix something now, then try to do it with a lot more data later.

Thank you.

Sue

John,
I’m off to an important meeting this morning, but will review the instructions for uploading a portion of the database. As these are historical records from 100 years ago, I don’t have to worry too much about privacy.

The txtConcatNames field is unique to the tblNames. tblNames is the one-side to the many on tblItemListWNames. Because of how my partner in this project uploaded pdf files (more than one page per item), there is a txtConcatItemsPg field that is the one side of tblItemsWProcesses and many to tblItemListWNames. Someone I know who has experience with SQL recommended that, and it is working well. Access would consider any entry without something in the given name field as nothing particularly and so I couldn’t create a link. I’ve added the phrase NotStated or Company (we have businesses listed also) to the txtConcatNames field to make it clearer.

My prior Access experience was dealing with living people who all had something common to put them in the database. This has been way different. Add in a 5 year gap in using Access and the new style to Access, and it has been a real challenge.

Sue

Wrong guess as to which forum. I know that making the change is done in the operating system, so went with that.

For Doc Brown, I had thought about using Open Office exclusively, but it didn’t work with an important aspect. I’ve spent the last couple of months learning a lot about tab delimited, CSV, UTF-8, imaging, digital cameras, color separation, book cradles, photo organizing software, Library of Congress terms, Dublin Core Meta Data Standards, and local history issues that I can’t remember exactly what the problem was, but it was a show stopper.

I’ve updated my profile to allow for Private Messaging.