How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask flopcat98 Your Own Question
flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 9077
Experience:  degree in comp sci. 20+ years install work with networks for small and large companies
Type Your Microsoft Office Question Here...
flopcat98 is online now
A new question is answered every 9 seconds

Thank you list" report stops at 7/30/2012 but table goes to

Customer Question

"thank you list" report stops at 7/30/2012 but table goes to 12/2/2012

Using Access 2007

query using two tables-donations and current roster 2010

On July 30, donations table was exported and imported back in to correct upper/lower case and then autonumber was gone

Submitted: 4 years ago.
Category: Microsoft Office
Expert:  flopcat98 replied 4 years ago.
How long is the report ? The simplest explanation is it hit the end of the page and you need to hit something to get the next page out.
can you send me the file if it is not confidential. You can post it on and post the link for it here.
Customer: replied 4 years ago.

can I upload to skydrive

Expert:  flopcat98 replied 4 years ago.
if i can get it down from there. i usually use wiki but if you can give me a link to download it thats fine.
Customer: replied 4 years ago.
Expert:  flopcat98 replied 4 years ago.
got it. what report is having the problem with what query
Expert:  flopcat98 replied 4 years ago.
I dont see a problem with any of them. They are sorted by group not date. The last group happens to not have any dates later than the 7/30/11 previous groups do have dates later than that.
the only one that did not was the thank you one and that is filtered for 1 particular date in the querry
Customer: replied 4 years ago.

they should have dates up to 12/2/2012

groups are correct

Expert:  flopcat98 replied 4 years ago.
which report ?
for all of them i see dates at 11/12 for at least one fund just not the last fund on the report
Customer: replied 4 years ago.

as noted in my initial question:

Thank you list REPORT stops at 7/30.

It should end with the same dates as the DONATION TABLE or DONATIONS TO TREASURER REPORT.

Thank you QUERY does not list addresses after 7/30.

Expert:  flopcat98 replied 4 years ago.
Got it. You have mismatched records and/or the wrong join type.
There are records in the donation table that dont match the other table on last name and first name
If you click on the joins between the table and set it to all records from donations and matching from address table you will get all the records. For whatever reason those later dates do not have matching records in the address table. So if you have the joins as only matching both tables you dont get those records.
Customer: replied 4 years ago.

i dont see any change after doing that

send me the report you have

Expert:  flopcat98 replied 4 years ago.

You dont see the change because you have it filtering for one specific date. i took that off too. It is uploading i will post it in a minute 2004 - TEST DB.mdb

Expert:  flopcat98 replied 4 years ago. 2004 - TEST DB.mdb
Customer: replied 4 years ago.
I'm sorry I am in bed now and will work on this in the morning.
The report was all dates after July 28.
It should have all the same entries as the donation table so we can send acknowledgements. And all the tables do match for all those entries.
Expert:  flopcat98 replied 4 years ago.
no they dont. if they did it would have worked.
as soon as i got rid of the filter that was giving only 7/28 i saw what you did. where it stopped at the 30th
when i changed the join type i got them all.
the only reason for that to happen was those dates did not have exact matches in the address table. Perhaps you linked to the wrong table ?
I did not look deep enough to see why the join would fail exactly but it did fail to match the first and last name from the donations file to the address file for those dates after 7/30
Customer: replied 4 years ago.

getting closer

names and dates show after 7/30

but addresses do not

Expert:  flopcat98 replied 4 years ago.
no they dont. if they did it would have worked.
as soon as i got rid of the filter that was giving only 7/28 i saw what you did. where it stopped at the 30th
when i changed the join type i got them all.
the only reason for that to happen was those dates did not have exact matches in the address table. Perhaps you linked to the wrong table ?
I did not look deep enough to see why the join would fail exactly but it did fail to match the first and last name from the donations file to the address file for those dates after 7/30
Expert:  flopcat98 replied 4 years ago.
that is exactly what i said. It is now a data problem. Those records do not match first name and last name between the donations file and the address file. That is why they were not printed at all when i had the join the other way.
If you really want me to i can look at it and tell you why. But it should be pretty obvious if you look at one of the records in each file. Really matching on name is ***** ***** great way to do it unless you pull the name from the same place originally when you create both files. I noticed they were all in caps in the donation file on those records. That would do it if they were not in caps on the address file. But even an extra space or mis spelling will screw it up. Its just not a good thing to match on when they are typed in seperately. a middle initial or a period a miss spelling anything will screw up the match. That is why most programs have a customer ID number that they match against instead.
Customer: replied 4 years ago.

names are ***** ***** they are created from a lookup from the current roster table,

Expert:  flopcat98 replied 4 years ago.
I will look at them later but they are NOT being matched that way. Perhaps the field size is different or there is an extra space in one of them.
Expert:  flopcat98 replied 4 years ago.

The problem is that it was matching against a query rather than the file. Honestly i dont see any reason it should not work that way as all the query did was rename the fields but it did screw up the merge. Probably has to do with how large the query result field was compared to the original data file field. Or maybe there is a filter in it somewhere that i didnt notice. In any event i see absolutely no reason to have the query at all. just link to the original database. 2004 - TEST DB.mdb

If it is missing fields you have to rename them to use the original data base fields rather than the query fields but in my quick eyeballing it looked ok. the other thing you might do is to not switch the field names in the query but i see no reason to have the query there at all. none of the other reports used it.

Customer: replied 4 years ago.

I've already tried it with the table and not the query.

if you can't help then say that

in any the other reports, if I add a date filter, it messes up the names

Expert:  flopcat98 replied 4 years ago.

The problem is with your Data

You have multiple records for the same first and last name and some of them have no address in them

see below


Customer: replied 4 years ago.

I know about the multiples, those parents have had multiple children die. The bogus zip code takes care of that. In another version of the table, those have been removed and it makes no difference. The table has been used that way for 10 years. When someone else tried to export data and import back in, things went bad.

The lookup columns don't work. Table preferences are goofy. I don't know how to get it back.

Expert:  flopcat98 replied 4 years ago.
No the bogus zipcode is not going to take care of anything because you havent filtered on it.
And your data is horrible. I do work for a company in new york that is in the same business. I do all their programming work in my private practice. They have hundreds of thousands of records and believe me if the data looked like yours they would be out of business.
I am opting out. I have done everything you asked and gone way out of my way for you even after I fixed your original problem. If i was allowed to do so i would charge you a couple of thousand to go in and straighten it all out. I wont do it for this price.
You have a nice day and i suggest that you have a professional come in and write you a real program. This one is obviously not professionally designed and it shows in the flaws you are trying to overcome with bandaids.
Expert:  flopcat98 replied 4 years ago.
The way you deal with multiple children or multiple events with the same parents is easy
you have one record for each event or each child
you link it to ONE address record with the parents. that is the way relational databases work. Its the same parents the same address. There is no reason to have more than 1 record. The entire idea of a relational database is to work that way.
so that if you have say a business that sells oranges and apples you have one record for apples and one record for oranges with a description and the price etc.
in another table you have the sales with many records for each orange and they link to the first record to get the color and weight and price and anything else that is the same for every orange.
You are not doing that here but you are still trying to link tables that are basically stand alone tables and not designed as a relational database. If you have managed to jury rig a few reports out of it fine. But it is NOT designed correctly and you are going to have problems using it to any real extent or expanding it unless you get it designed properly and the data matching what it should be. Having 3 records for 1 family half of which have no data in them is going to cause problems. That would be obvious to any professional programmer. You are running into them now whatever you think you can blame the actual start of the problems on that is the root cause.
Customer: replied 4 years ago.
it's a mess
How to start over?
It was one big table originally.
I have to get this into order.
Other than get rid of extra parent records, I'm stuck. How to relate parent, child and donations?
Expert:  flopcat98 replied 4 years ago.
what is the actual events that you are covering and what data do you need to track ?
one table would be for the parents with their names and address and i suggest an id number that can easily be linked to. that way the other records you can put just the id in and link to pull up or select from the parent table
after that i need more information on how the business runs.
Customer: replied 4 years ago.
Roster has ID and child has that ID in assigned field
Primary should be child and not parent? Many relatives from other addresses may attend and donate for one child
Mailing lists
Photo in program
Brick purchased
This is a support group for parents after a child dies
Expert:  flopcat98 replied 4 years ago.
You have to back up and explain what business you are in and what you need to track. I work with a company that does benefits and sells tables and takes contributions and sends mailings etc so its pretty similar i think. But i need to know what exactly you do and what you track from the beginning
get it worked out as well as you can in your head i am actually heading for bed but i will look it all over when i wake up.
Customer: replied 4 years ago.

Main activity is monthly support group.

Track attendance.

Contact information.

Receive donations.

Parents, grandparents and adult siblings attend for child who died.

Some have more than one child who died.

So multiple addresses linked to multiple children.

Membership records kept after families are inactive, for purposes of people served over the years. People also return or update address later.




Cause of death



Memorial Website


sent to families until they have not been active for 2 years.

Active = attendance or donation or permission to print child information.

List of births/deaths by month.

Cards sent for deaths by month.

Permission is tracked to use child's name in newsletter.


1-3 events a year. Permission given for children entered in slide show.

Photos of children kept on file.


Memorial bricks sold. Some bricks ordered by families who have never attended the group. Some families have multiple bricks for a child.

Corporate Gifts

Donations received, no connection to a child.

I have made some changes if you want to see the updated file.

file as it is now 2004 - TEST DB.mdb

Thank you

Expert:  Rachel-Mod replied 4 years ago.


I’m a moderator for this topic. It seems the Professional has left this conversation. This happens occasionally, and it's usually because the Professional thinks that someone else might be a better match for your question. I've been working hard to find a new Professional to assist you with your question, but sometimes finding the right Professional can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Thank you!


Customer: replied 4 years ago.

yes continue

Expert:  Rachel-Mod replied 4 years ago.
Thank you for your patience. We will continue the search for a Professional for you.
Expert:  flopcat98 replied 4 years ago.
Sorry I am here.
Like i said to totally redo it myself i would want a thousand or two. I am willing to give advice and look at file structures and stuff and hope you will give a bonus for what it is worth.
I would make the child the "primary"
each child is singular 1 child per record
parent info should be another table with 1 record for each parent or family member if you want mother and father as one record that is fine. grandmother or aunt should be separate record if they would contribute or attend independently.
ONE RECORD for each person or couple ONLY.
then a table for events.
that links to the child to provide who the event is for
it also links to the parent info file to get attendees and addresses
this file has things like whether they attend or how much they gave etc
Bricks can be their own table set up similar to the event file or can be part of the event file with different event name depending on how similar the information that you need is.
corporate gifts sounds like it can be stand alone as it has no child attached it might link to the parent table if they are contributers not if they don't or do it very rarely.
I recommend id numbers for each parent record and child record. Its easier to make sure that you get a good match on that vs name. In access its possible to do it on name though but you have to be very careful not to get duplicates. and its better to have one field for full name if you want to link to it rather than linking on first and last.
Customer: replied 4 years ago.

sorry I think i missed a few messages yesterday

this was my original problem and it has not been solved because no addresses show on the thank you report, whether it comes from a table or query.

"thank you list" report stops at 7/30/2012 but table goes to 12/2/2012

I appreciate that you have provided much explanation, all of which I already knew, and still no direction of how to re-write the link to get the data needed.

this is a non-profit that I am volunteering for, so there is not $1000-$2000 in the picture. I went back through your notes, have made changes as suggested but the duplicate records have nothing to do with the problem.

the lookup fields are gone after the export/import. Can they be regained?

are you suggesting that I re-enter all the data or export/import into a new table.

thank you

Expert:  flopcat98 replied 4 years ago.

the addresses dont show because it is linking to a record with no address. delete those addresses and the address will show. In fact it IS showing the address but the address on that record is blank. Again that is not a programming issue its a matter of getting rid of the bad records or putting addresses into them.

do a query that creates a new table after filtering out those records with no address

then match against that table

Expert:  flopcat98 replied 4 years ago.
It is not matching some of the records. I dont think that it is only the ones after july either. Just that all of those have the problem. I think it is a problem with the data its not a programming problem. I can try to look at it later but i am guessing that there is some sort of non visible control code in some of the record either name or date.
I need some time to think of a way to get a better look at the fields
You can relist it if you want. Maybe somebody else works with access a lot more than me and can find it.
Customer: replied 4 years ago.

it used to work

something to do with the first/last name lookup field became a text field when it was exported/imported

when the thank you report shows the entries up to 12/2/2012, same as table, with addresses, I will rate to finish.

some records (corporate donations) will not have a child match and will not be on the list for the time being. We write those thank yous separately, but they are in the table to track donation amounts.

Expert:  flopcat98 replied 4 years ago.
that could be it but i tried changing one manually setting the date from the date box and it still didn't work. Might have to switch everything to text or create a new table. I will work on it when i have time but while i used to do a lot of database programming i never did much with access so it is going to take me a little while to find a couple of hours to experiment on it.
If you want to relist you can. But i will stick it out with you if you want.