Help with a table

[advomystics]

I am creating a table that will eventually have information for my invoices for a dance studio. I am not sure how to set up the table so it will pull the correct information into the invoice.

I have a table (tblDanceClasses) set up with the dance classes and their respective price.

This table which I have labeled charges (tblCharges) needs to pull the class name and the cost from table (tblDanceClasses) to (tblCharges). Can you get it to pull information from 2 columns in tbl DanceClasses to 2 columns in tblCharges?

I hope this is clear as mud

 

[Ranman256]

the table tblDanceClasses, should have a DC ID, (autonumber)

This ID should be used in tblCharges:
[DcID] long (from tblDanceClasses tbl)
[ClientID] long (from tblClients tbl)
[AmtChg] currency
[AmtPd] currency
[PdHow]
[DatePd]

maybe event a tblPayments
in case they make installments/or multi payments.

 

[advomystics]

So for this table I only want the charges not who paid and how much.
This is my table set-ups

tblDanceClasses
[ClassID] AutoNumber
[ClassName] Short Text
[ClassStartTime] Date/Time
[Teacher] Number (from tblTeachers)
[Cost] Currency

tblCharges
[ChargeID] autonumber
[ChargeName] Short Text
[ChargeAmount] Currency

Under Charge Name I have items like Registration, Returned check fee, 8 different competition fees. I also want to include class fees. Can I pull the class info from tblDanceClasses into tblCharges? or should I make a line for each dance class with the corresponding class cost?

 

[sneuberg]

You could set up a combo box with the row source set to the tblDanceClasses and with a button or the combo box afterdate could add a record to the tblCharges, perhaps sticking the ClassName into the ChargeName, but in this scheme there is no information about who is being charged. I think you need to develop your structure a little more.

 

[advomystics]

Thank you. I am very new to this. I just got Access for Dummies and am going through it too. I am hoping to create a business program for the Dance Studio that I work for. They are using old programs and paper systems. I love computers and am starting to like Access.

 

[sneuberg]

I just looked at that book on amazon.com and while it might be good to get you going in Access it appears weak in how to structure your data. It is very important to get your tables structured correctly before you do anything else. Not just making sure that all of the data you want to track is accounted for, but that it is in what they call normal form.

The first database I developed was back in 1982 using db2 and like you I had the manual that described how everything worked in db2, but that manual did not have two words about normalization. It was a database to track students and their tests at an Air Force NCO Academy. So I proceed to make a table for the students and one for the grades with columns for Test 1, Test 2 etc (typical example for non normal form) . I got the program working and they were so happy with it they gave me an award. Many years later when I learned about normalization I felt real embarrassment for what I did and I still hope the program was discarded before someone looked at the structure and saw the crappy system I put my name on.

So don't be like me. Learn about normalization. Just google "database normalization" and read up on it.