Report register help? (1 Viewer)

J2thaE

Registered User.
Local time
Today, 21:49
Joined
Sep 7, 2016
Messages
13
I'm quite a newbie when it comes to creating access databases. I've used the basic contacts template Microsoft has used and altered that to suit my needs. I've been trying to create a method of this database automatically showing people on different reports.

On the Contact Details form, I have a subform that shows 6 columns consisting of different venues (for example: Sports Centre, Sportspark, etc.). These 6 columns are comboboxes that house these venues.

What I also have is about 10 reports that I need working as registers, for attendance, for different venues. These registers should show the people who attend certain venues and it should populate. For example, if someone attends "Sports Centre", their name should appear on the Sports Centre report.

However, at the moment, I cannot get it to work. I've tried using queries, tried vba and even Macros. But no success. In the end, it just adds every person on every report.

Can anyone suggest any ideas for what I could try next? If any extra information is needed, I'll try the best that I can to help. :)
 

plog

Banishment Pending
Local time
Today, 16:49
Joined
May 11, 2011
Messages
11,612
Can you demonstrate your issue with data? Provide 2 sets:

A. Starting sample data from your table(s). Include table and field names and enough sample data to cover all cases.

B. Expected results. Show what you hope to end up with, based on the data you provide in A.
 

J2thaE

Registered User.
Local time
Today, 21:49
Joined
Sep 7, 2016
Messages
13
The name of the table is called 'Contacts'. What I want displayed on the reports are the First Name & Last Name. The 6 columns that house the venues, they are called Monday, Tuesday, Wednesday, Thursday, Friday & Saturday. This is what each of those 6 columns house. Mon: 'Sports Centre' & 'Diamond'. Tues: 'Willow' & 'River'. Wed: 'North' & 'Southgate'. Thurs: 'North W' & 'Norman'. Fri: 'Ale' & 'Eaton'. Sat: 'Eastern' & 'Western'. All Monday, Tuesday, Wednesday, Thursday, Friday & Saturday are comboboxes.

What I hope to end up with is whoever has for example, have Monday selected as Sports Centre. I want their first name and last name to appear on the Sports Centre report, only them. Likewise for the other data in Monday, Tuesday, Wednesday, Thursday, Friday & Saturday.

I hope this is enough information. If you need anymore info, I'll gladly help.
 

plog

Banishment Pending
Local time
Today, 16:49
Joined
May 11, 2011
Messages
11,612
Sounds like the issue is your improperly structured table. Field names shouldn't hold specific values. Your field names should be generic and you should hold values in the table itself.

That means instead of this:

FirstName, LastName, Monday, Tuesday, Wednesday, ....
John, Smith, Diamond, Willow, Willow, ....
Steve, Jones, Western, Diamond, Eastern, ...

You should have a table like so:

FirstName, LastName, VenueDay, Venue
John, Smith, Monday, Diamond
John, Smith, Tuesday, Willow
John, Smith, Wednesday, Willow
Steve, Jones, Monday, Western
Steve, Jones, Tuesday, Diamond
Steve, Jones, Wednesday, Eastern

That's how your table should be structured. You should have 7 records for a person for a week, not 1 record with a field for every day of the week.

When you get it like that you can easily run a Monday report, a tuesday report, a wednesday report. Heck you could even run a WeekEnd report that includes Friday-Sunday.

Fix your table and this problem solves itself.
 

J2thaE

Registered User.
Local time
Today, 21:49
Joined
Sep 7, 2016
Messages
13
Hi, I tried doing that, creating VenueDay & Venue. I went to a contact and selected this:

Venue Day , Venue
Monday , Diamond
Friday , Ale

But it came up with an error saying: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship." I put the indexed option in the table's design view to Duplicates OK, but it's still saying it.

What should I do next to overcome this problem?
 

plog

Banishment Pending
Local time
Today, 16:49
Joined
May 11, 2011
Messages
11,612
You should create a new table and move your data from the old one to the new one. You have placed restraints on the old table that probably only allows 1 record per customer. Rather than hammer your old table to the new format, just build a new table and move your data from old to new.
 

J2thaE

Registered User.
Local time
Today, 21:49
Joined
Sep 7, 2016
Messages
13
That has allowed a person to have multiple days and venues, but however something else has popped up. I should've really mentioned this at the start to be quite honest.

I have one form that shows all of the people in a datasheet format called Contact List. I can open each person to see their specific details. When I go to add to a persons 'VenueDay & Venue' once, it saves on their Details page. However, when I add a second, it creates a new blank person on the Contact List.

I'm sorry that I'm continuously saying something isn't working, and I appreciate your help a lot.
 

plog

Banishment Pending
Local time
Today, 16:49
Joined
May 11, 2011
Messages
11,612
Forms are the last part of the process. Sounds like you started there and that's why you can't get the data out like you want.

Fix your tables, then move on to reports, then finally the last step is setting up your forms.
 

J2thaE

Registered User.
Local time
Today, 21:49
Joined
Sep 7, 2016
Messages
13
Ah I see. I've switched the tables and created a new one called 'Members'. I couldn't copy the contents of the table Contacts for some reason which it wouldn't tell me, but I guess I'll have to figure it out at a later point. Do some tweaking and moving about.

What is the next thing that I should do for people showing up on different reports?

Again, I'm sorry that I'm asking a lot of questions. I've been trying to fix this problem for ages.
 

plog

Banishment Pending
Local time
Today, 16:49
Joined
May 11, 2011
Messages
11,612
Can you post your database? or give me a list of tables/fields along with sample data? If you just want to type data into your post, use this formatting method:

TableNameHere
Field1Name, Field2Name, Field3Name
Tom, Smith, 12/2/2010
Jane, Jones, 4/12/2014
Larry, White, 6/7/2009
 

J2thaE

Registered User.
Local time
Today, 21:49
Joined
Sep 7, 2016
Messages
13
Hi, unfortunately, I cannot post a link for the database because my post count is under 10 at the moment, because I made this account to try to fix this problem. What do you suggest I should do? Because the table is quite lengthy and a little in organised
 

plog

Banishment Pending
Local time
Today, 16:49
Joined
May 11, 2011
Messages
11,612
Type the table information into a posting using text. Use the formatting in my prior post.
 

plog

Banishment Pending
Local time
Today, 16:49
Joined
May 11, 2011
Messages
11,612
Yeah, lots of issues. You realy need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), that's the name for the process of setting up your tables correctly. Here's the big things that jump out:

1. Storing values in field names. This is the one I pointed out initially, and you've done it throughout your database.

2. Numerated fields. When you start numbering fields (e.g. Tick1, Tick2, Tick3, Venue 1, Venue 2, etc.) its time for a new table. Like fixing #1, you need to break this data off and store it in its own table.

3. Same structured tables. You shouldn't have tables with the same structure, when you do that, its like you are storing a value in your table name. Contacts and Members should not be in seperate tables. If there's a difference between a member and a contact, you would create a new field and store either 'Contact' or 'Member' in that record to denote if the record is for a contact or a member.


Again, you need to read up on normalization, work through a few tutorials then focus on fixing your tables. Then you move on to report building and finally creating forms.
 

J2thaE

Registered User.
Local time
Today, 21:49
Joined
Sep 7, 2016
Messages
13
Hi, thank you for having a look at the mess of a database ive tried to create. The numbered fields were from earlier iterations of the database, like a trial and error. I need to delete them.

And the contacts and members tables. The contacts table was the first table I had and the members one was the new table that was suggested to be made from earlier.

But I will definitely have a read of normalisation as I really want this database to be as working and fixed as can be! Thank you again for your help.
 

J2thaE

Registered User.
Local time
Today, 21:49
Joined
Sep 7, 2016
Messages
13
Hi, I've had a read up of normalisation from the link that you gave me. Had a look at some YouTube tutorials too. I slightly understand how it works however, I'm not too sure how to implement it into my database.

I've been making this database for ages, but have limited knowledge of it, so I'm not too sure what exactly is the best thing to do :/
 

plog

Banishment Pending
Local time
Today, 16:49
Joined
May 11, 2011
Messages
11,612
Dive in. Start a new database and build the tables you think you will need. No need to move the data over or re-structure your existing system. Just build the tables you think you will need. Set up the relationships in the relationships tool, then post a screenshot of it back here and we can work on it.

Be sure to the address the numbered points in my prior post about the big issues I've identified.
 

Users who are viewing this thread

Top Bottom