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
flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 7547
Experience:  degree in comp sci. 20+ years install work with networks for small and large companies
23372949
Type Your Microsoft Office Question Here...
flopcat98 is online now
A new question is answered every 9 seconds

RA RR

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: 1 year ago.
Category: Microsoft Office
Expert:  flopcat98 replied 1 year 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
www.wikisend.com and post the link for it here.
Customer: replied 1 year ago.

can I upload to skydrive


 

Expert:  flopcat98 replied 1 year ago.
yes 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 1 year ago.
Expert:  flopcat98 replied 1 year ago.
got it. what report is having the problem with what query
Expert:  flopcat98 replied 1 year 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 1 year ago.

 


they should have dates up to 12/2/2012


groups are correct

Expert:  flopcat98 replied 1 year 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 1 year 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 1 year 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 1 year ago.

 


 


i dont see any change after doing that


 


send me the report you have


 

Expert:  flopcat98 replied 1 year 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

 

http://wikisend.com/download/318196/ACTIVE 2004 - TEST DB.mdb

Expert:  flopcat98 replied 1 year ago.
http://wikisend.com/download/318196/ACTIVE 2004 - TEST DB.mdb
Customer: replied 1 year 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 1 year 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 1 year ago.

 


getting closer


 


names and dates show after 7/30


but addresses do not


 


 

Expert:  flopcat98 replied 1 year 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 1 year ago.
Yes 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 XXXXX XXXXX 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 1 year ago.

 


 


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


 

Expert:  flopcat98 replied 1 year 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 1 year 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.

 

http://wikisend.com/download/157228/ACTIVE 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 1 year 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 1 year 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

 

graphic

Customer: replied 1 year 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 1 year 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 1 year 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 1 year ago.
Yes 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 1 year 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 1 year 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

Donations
Mailing lists
Attendance
Active/not
Photo in program
Brick purchased


This is a support group for parents after a child dies
Expert:  flopcat98 replied 1 year 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 1 year 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.


 


Child


Birth


Death


Cause of death


Photo


Brick


Memorial Website


 


Newsletter


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.


 


Events


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


Photos of children kept on file.


 


Bricks


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


http://wikisend.com/download/318196/ACTIVE 2004 - TEST DB.mdb


 


 


Thank you


 


 


 

Expert:  Rachel-Mod replied 1 year ago.

Hello,

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!

Rachel

Customer: replied 1 year ago.

 


yes continue

Expert:  Rachel-Mod replied 1 year ago.
Thank you for your patience. We will continue the search for a Professional for you.
Rachel
Expert:  flopcat98 replied 1 year 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 1 year 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 1 year 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 1 year 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 1 year 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 1 year ago.
yes 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.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
flopcat98
flopcat98
Microsoft Office Technician
7547 Satisfied Customers
degree in comp sci. 20+ years install work with networks for small and large companies