You are here:

Using MS Access/Subform design to hold multiple entries off the same table w/o blanks

Advertisement


Question
TOL
TOL  
I'm looking for how to design a subform to look like a paper form we have and the ability to run reports after. At first I set up the necessary information across rows and data entry went smoothly until I wanted to run a report so I have since reconfigured all the necessary information into one column and now I'm struggling with how to design it for the subform. Here are my details: We have a master donation database for two main events we do throughout the year. I have a table set up for the donors and for all of the charity dinner and tree of lights (separately)information. Tree of lights is where I'm struggling because I need to be able to enter a different amount of names and tree locations for those who donate. I know it is similar to a recipe card, I have the donor id on the form set up with a one to many relationship. What is the best way to design the subform to be able to enter multiple names by different donors and not show blank spaces when I need to run a report by tree locations. Thank you so much for your help in advance, I feel like its at the edge of my finger tips, just have been 'staring' at it way to long!

Answer
First, let me apologize for the delay in responding. I must have missed the first notice of your question.

It sounds like you had your tables done right, then you changed them to match the output. You should not confuse data storage with presentation. They are two different things.

Unfortunately I can't read your screen shots. Can you upload them separately to cloud storage like OneDrive. Or just show me the Relationships window with each table expanded.

Based on what you have said, I think you need 4 tables.

tblDonors: DonorID (PK autonumber), names, address, other info about each donor.
tblEvents: EventID (PK Autonumber), EventName
tblEventSchedule: ScheduleID (PK Autonumber), EventID (FK), EventDate
tblEventDonors: EventDonorID (PK autonumber), EventID, DonorID, Donation

You have a many to many relationship between events and donors, that's what tblEventDonors models.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.