Initial Table Setup for invoices/reservations

[Spiritwoman]

Hi. I'm bumbling along in Access and would appreciate your help. I think I am in way over my head here, so any point in the right direction would be great.


I'm creating a reservation database for a church that creates a reservation for a fee per hour, adds an amenity like a stage extension for a flat fee, adds a service like a musician for a fee per hour. I would like it to calculate the total on an invoice and track payment history.

I currently have these tables:

Contact (ContactID PK)
Rooms Available (RoomName PK)
Services (ServiceID PK)
Amenities (AmenityID PK)
Payments (PaymentID PK)
Reservations (ReservationID PK)
Invoices (InvoiceID PK)

I read another thread the suggested breaking the invoices into two tables, so I created:
tInvoice (tInvoiceID PK)
tInvoiceDetail (tInvoiceDetail PK)

(However, I'm not sure what to do with these two new tables now that I've created them.)

Help?

 

[georgedwilkinson]

Ignore what you read, you were on the right track. Just need a little adjustment.

Keep your Invoices table. Create a new InvoiceDetails table. In the InvoiceDetails table, create a column for the InvoiceID. Go into the relationships tool and drag InvoiceID from Invoices to InvoiceID in InvoiceDetails, thereby creating a foreign key.

Tables should generally be named plural noun names.

For the table "Rooms Available", drop the word "Available". Available is a status, not a noun. As a general rule, don't put spaces in table or column names (you'll thank me later).

There is no difference between services and amenities, especially from a billing perspective, and thus, you don't need 2 different tables. If you need to differentiate between the 2, add a type column to the table.

How are "Payments" and "Invoices" related, in real-world business talk?

If you need to know a room is "not" available, how do you distinguish bookings? Wouldn't there be an "Events" table that ties together the room, amenities, people, invoice, etc.?

This is a pretty ambitious project. I'll be happy to help as I can.

 

[Spiritwoman]

Great!

Thanks for the speedy response!
I removed all the spaces from names and titles.

I broke services and amenities into two tables because rental fees for amenities go to the church but service fees go to independent contractors. I thought- in the long run- it would be better to have those seperate. They also track different info, like purchase date and replacement cost versus contact info and minimum time requirements. I can certainly combine them if you think that is best.

In theory, Invoices generates a total to quote a client, and the Payments records all the money coming in or expected. My thought process was that would make it easier to search for all unpaid balances or sum all income this month, etc. Should they be together on one table?

I created an Events table to link all those fun things. It's a feature they wanted but I hadn't come up with the solution yet

I deleted the unneccessary tables and created InvoiceDetails which now has a foreign key link to Invoices.

I know its ambitious, especially for my beginner level. I appreciate the help.

 

[georgedwilkinson]

Any time. Do you need additional help now?

I get what you're saying about services and amenities...makes total sense. Since they are both likely to be items on an invoice, though, you might want to make them polymorphic...i.e. create a 3rd table with a 1:1 to either Amenities or Services and a discriminator column. Another way to achieve this is to put all the columns from all 3 tables into a single table, including the discriminator column. The first method is my preferred method.

Yell if you need help.

 

[Spiritwoman]

Once my tables are setup correctly, is it better to make the invoice using linked queries or using form/subform?

I've tried the form/subform, which displays all the info for whatever I set as parent form, but there is always an error with the child form and it shows blank once I leave design view.

Should I be doing this as a query instead?

I'm not sure what my next step is.

(I deleted my Amenities table and just added the info to the Services table instead.)

Thanks!

 

[georgedwilkinson]

You need to set up a form/subform.

You can get pretty far along using the form wizard. Just select the tables (Invoices & InvoiceDetails) and associated columns that you want the form/subform based on and the wizard will give a pretty decent first pass that almost always works (as long as you have your relationships set up correctly.

 

[Spiritwoman]

I setup the Parent form to use data from the Reservations table. The child form references the Services table.

I can create a new reservation from here, but I cannot add a Service. It behaves as though I'm trying to create a new service rather than select an existing one.

In the child form, the Services field is not a dropdown box and I think it should be. I've played with property settings (control source, format, etc) but it results in various errors.

I'm searching for answers in previous forum posts, but I think I'm lacking the vocabulary to find exactly what I'm after.

I'm guessing these are pretty standard steps if I had a good manual to follow. I've searched a bit online but what I find is too advanced for me but my beginner books don't go this far in depth. Any good resource ideas?

 

[Spiritwoman]

I also deleted all my relationships and started from scratch in a more logical manner, so that fixed the problem with the child form not showing up. Thanks.

 

[Spiritwoman]

Re-read your last note and followed your directions and guess what- it worked better

When I select a ServiceName, how do I make it automatically fill in the appropriate cost? Currently, it offers me a drop down of costs to choose from.

I'm making progress! Thanks.

 

[georgedwilkinson]

There are many good books but describing your problem on the forums will help you understand the answer a little better.

Have you figured out how to use the visual query editor yet? That might be helpful to you for working with combo-boxes.

Easiest way to put combo-boxes on an existing form is, in design view for the form, right click on the control that is bound to the column you want as a combo, select "Change To|Combo Box". Then select the properties. On the "Data" tab, for "Row Source" click the button with the "..." and select the table that has the description from the "from" table. Select the "ServiceID" column and the other column that has the description. Exit and use the query on the control. Make sure that "Bound Column" is 1.

On the "Format" tab, "Column Count" = 2, "Column Widths" = "0,1".

That'll just about do it, as long as the relationships are set up right.

Let us know if you need more help.

 

[Spiritwoman]

Only able to add one line item for Service

Hi again. Doing great with your help and setup a snazzy form with subform that adds a room and calculates the fee plus a prorated fee for additional hours. I can also add a service and all the calculations work as well. (Yay!)

However, I can only add ONE service. I tried duplicating the fields, but changing one changes them all. Any ideas?

Also, I am trying to get the main form info to pull from a query or autofill in some other way. Current, you can type in the Group Name, Contact Info, etc. I'd prefer if you selected the Group from a combobox and all the corresponding info would fill in. I've tried changing the Group field to a combobox and specifying the data source to the Contacts table or a Contacts Query but the dropdown is always empty. I specified that it should have 2 columns and look in column 2 for the info but nothing. Am I missing a step?

Thanks!