Showing all records regardless of relationships

[moorsey]

Hope I can describe this well enough.

I am creating a DB for a youth organisation (Air Cadets), details of which are stored in tblMembers. They attend twice a week, each of these days is called a parade and on each parade certain members are given a duty, so the date of the parade as primary key and the members having duty are stored in tblParade.

Aswell as this, individual attendance of each member is stored with reasons from non-attenders, this uses the member number and the parade date as a composite key, no member could attend twice on any one night.

So I am trying to create a register type form, which lists all stored members who can then be ticked off if they are attneding e.t.c.

But I'm not entirely sure how to do this, I have created a query with the bits of data I need, and can enter the members in individually, but can't seem to get a continuous form which lists everyone in the members table, regardless of if they have any entries in the attendance table.

Hope that makes sence,

Martin

 

[ted.martin]

You need to create a query with one-to-many relationships. If you post the file, I will take a look at it for you.

 

[moorsey]

all the relationships should be in properly, I did attempt normalisation! But thats another story, confusing as anything

thanks for taking a look

 

[ted.martin]

Why not just create a simple query from the tblMembers with effectively becomes your register.

Add some extra columns if you need; called NewField1 and NewField2 to represent the Parade Date and the Attendance.

Make this Query an Update Query to a new table called Register.

Then using code in a separate module - you can use the Dlookup function to interrogate this Register table to APPEND the data to the appropriate other Parade and Attendance records.

Hope this makes sense. I am also wondering whether in your relationships window, the join between tblMember and tblAttendance should be type (2) not type (1)

 

[moorsey]

hmm, ok, not sure I entirely understand.....

I changed the relationship type, thanks for that, I have never even medeled with the types before, but then again, this is the most advanced database I have ever done

If i open the attendance form on its own, it displays everything in the members form which is fine

But when I open the parade form which has attendance as a subform then nothing is displayed, but when i add a date and enter the member number then the details all pop in which is great, so the relationships must be working and the data is there, its just getting that data to be displayed when the form loads, I guess there must be some kind of recordset function that displays everything.

I see where your coming from with the query, I just think its a bit beyond me and that it could be done with less "messing" maybe?

 

[ted.martin]

The trick is to keep things simple in the design stage. You may find that combining the Parades and Attendances onto one form will make the reporting easier.

 

[moorsey]

yeh, originally it was all one, but normalisation put them into seperate tables to remove repeating data

still open to suggestions

 

[moorsey]

anybody any suggestions?

 

[moorsey]

sorry to bump this, but I'm really struggling with it