Basic Database Design Help

[jrbedwell]

Hi all -

I'm trying to design what I think should be a pretty simple database, but I'm having a number of issues.



I run a beach house in the summer, with about 25 members. There are 15 weekends in the summer, and each member gets to choose 7 of those weekends. I've got a member list built in a table, and I'd like to have a form where I can pick the member name from a list box, and then check off the weekends they have chosen from a list of the 15 weekends. From there, I'd like that data to dump into a table where I can run a report for all weekends or just selected weekends showing which members are scheduled for each weekend.

I hope this makes sense. Any advice or resource files/links would be greatly appreciated. Thanks!

[ranman256]

youd have the tMembers table,
tWeekends table
&
tBooking table
---------
[MemberID]
[Weekend]
other fields...

the form would have a combo box to pick Member
a listbox would show all the available weekends.
dbl-click on the listbox , this runs an append query to add the 2 items to the tBooking table.

similar to this:

[John_G]

What you have here is a many-to-many relationship between weekends and members. Each weekend can have many members, and each member can book many weekends.

To implement this you need two more tables: one for the list of weekends, and one for the member reservations. This latter table is called a junction table in database lingo, and each record in it will contain a booking by one member for one weekend (at least that information - you might want other fields in there as well, that refer to that particular booking.

Your data entry form will relatively simple - it would have the junction table as its record source, and you could select members and weekends from dropdown combo boxes.

For reporting, you don't need an additional table - you can just join all three tables in a query, and base your reports on the query. The same query can be used for both reports (by member or by weekend); you would just do the sorting differently in each report.

[davegri]

This db will give you a form with member combobox dropdown, and 2 listboxes. The listboxes contain weekends, one with available weekends for the member and the other, chosen weekends for the member.
You can move weekends between listboxes with the arrow buttons. Included are reports for the chosen member and for all members.
MembersWeekend.zip

[jrbedwell]

This db will give you a form with member combobox dropdown, and 2 listboxes. The listboxes contain weekends, one with available weekends for the member and the other, chosen weekends for the member.
You can move weekends between listboxes with the arrow buttons. Included are reports for the chosen member and for all members.
MembersWeekend.zip

First of all, thanks to all for the responses and assistance!

This DB is pretty much what I was trying to do. Just had a couple of questions, though.

- I'd like the reports to be both by weekend and by member. So, I guess 4 report options: individual weekend, all weekends, individual member, all members. How do I change that?
- Minor annoyance, but in the form, I'd like the weekends to be in order that they are in the table, 1-15, May-Sept. They're currently sorting alphabetically, and I can't figure out how to fix that. How can I change that?

Much appreciate the help!

[davegri]

- I'd like the reports to be both by weekend and by member. So, I guess 4 report options: individual weekend, all weekends, individual member, all members. How do I change that?
- Minor annoyance, but in the form, I'd like the weekends to be in order that they are in the table, 1-15, May-Sept. They're currently sorting alphabetically, and I can't figure out how to fix that. How can I change that?

Can you post your DB so that I can see what's in your tables? I can transfer that data into the new database and work with that data to get the results that you want.

[jrbedwell]

Can you post your DB so that I can see what's in your tables? I can transfer that data into the new database and work with that data to get the results that you want.

MembersWeekend (2).zip

First time uploading, so hoping this works ...

[davegri]

First time uploading, so hoping this works ...

Got it. Working on it...

[ssanfu]

PMFJI,

Good job on the naming convention. I also use the "_PK" and "_FK" naming suffix for PK/FK fields.

I would suggest also using the "Me" keyword when referencing controls on a form (in the FORM module). It also lets you know that "lstAvailable" is a control, not a variable. And intellisence kicks in to ensure no spelling errors (for the control name).

Private Sub cmdAdd_Click()
    Dim varItem As Variant
    Dim sSQL As String
    Dim nRid As Long    'Resident
    Dim nEid As Long    'event
    Dim varRow As Variant

    If Len(Me.cboMembers.Column(1) & vbNullString) = 0 Then
        MsgBox "There is no Member selected.", vbOKOnly + vbInformation, "  I N P U T   N E E D E D   "
        Exit Sub
    End If

    If Me.lstAvailable.ItemsSelected.Count = 0 Then
        MsgBox "Please select at least one Weekend. You can select multiple items by " _
               & "holding down the CTRL key while clicking items.", vbOKOnly + vbInformation, _
               "   I N P U T   N E E D E D   "
        Me.lstAvailable.SetFocus
    End If
    nRid = Me.cboMembers.Column(0)

    For Each varRow In Me.lstAvailable.ItemsSelected
        nEid = Me.lstAvailable.Column(0, varRow)
        sSQL = "INSERT INTO tblJunction (Members_FK, Weekends_FK)"
        sSQL = sSQL & " VALUES (" & nRid & ", " & nEid & ");"
        'Debug.Print "Insert " & sSQL
        CurrentDb.Execute sSQL, dbFailOnError
    Next varRow

    Me.lstAvailable.Requery
    Me.lstChosen.Requery
    For Each varItem In Me.lstAvailable.ItemsSelected
        Me.lstAvailable.Selected(varItem) = False
    Next
    For Each varItem In Me.lstChosen.ItemsSelected
        Me.lstChosen.Selected(varItem) = False
    Next
End Sub

Minor annoyance, but in the form, I'd like the weekends to be in order that they are in the table, 1-15, May-Sept. They're currently sorting alphabetically, and I can't figure out how to fix that. How can I change that?

I added a field to the weekend table named "WeekendSeq" (Integer) and modified the code and the query "qScheduled".
Changed the query "qScheduled" - added the query "qMemberName" in place of the table "tblMembers" (the name concantation was already in the query "qMemberName").

Maybe this is (close to) what you are looking for.


@davegri - not meaning to step on your toes....... just my $0.02...

[davegri]

Here's a finished product, I hope. Includes reports and Main Menu form, plus forms to edit members and weekends. Applied a nice greyscale theme.

[isladogs]

Here's a finished product, I hope. Includes reports and Main Menu form, plus forms to edit members and weekends. Applied a nice greyscale theme.

That's a very nice example you've provided dave

Just a couple of minor issues which are easily fixed
1. On frmScheduled, clicking Report this member throws an error. The report is looking for qScheduled

2. If you remove nav pane and ribbon then close any of the forms using the x you are left with an empty database window.
Suggest using form close event on each to reopen frmMain

Great colour scheme for a winter's day or Emmageddon as storm Emma has been dubbed here in the UK

[davegri]

OK, polished thing up.
Added error handlers to all code events.
Added table to hold errors, and a report to list errors (Allen Browne)
Added Cascade deletes to relationships.
Added totals forms and totals button to scheduling form.
Added form close events as suggested by ridders.

MembersWeekend-davegri-v2.zip

Attachment 32843

[isladogs]

Looks very good. I hope the OP is pleased with it....

[jrbedwell]

OK, polished thing up.
Added error handlers to all code events.
Added table to hold errors, and a report to list errors (Allen Browne)
Added Cascade deletes to relationships.
Added totals forms and totals button to scheduling form.
Added form close events as suggested by ridders.

MembersWeekend-davegri-v2.zip

Attachment 32843

Ok, so this will work for my needs, and I don't want this to come off as ungrateful, because I really appreciate the efforts, but ...

I don't really understand the menu that comes up when I open the form. When I click the unclutter and reclutter screen buttons, nothing happens, and I don't have a need to edit the weeks, as they are set. Is there a way I can just get rid of that menu when I open the file? I'm really only going to use the frmSchedule form, and I'm not sure what the frmSchedTot does, as it gives me an error.

Again, really appreciate the work on this, and I'm hoping it will help me learn how to do some of this myself.

[davegri]

OK. Here's a stripped down version. I don't understand why you would get those errors. Any problem with this version, you can print the error report included and post that here...
MembersWeekend-davegri-v3.zip