Help needed with table design please please please.

[ArviLaanemets]

You can get done with classes table faster, when you use an append query.

Btw. don't use field names with spaces in them, i.e. instead "Class Number" use "CalssNumber" or "Class_Number".

Create a temporary table with a single field Class, p.e. tblTempClasses: Class, and fill it with all classes for one semester and course.
Create a saved query p.e. qClearTempClasses: DELETE FROM tblTempClasses
Create a saved query (I created the example for semester with ID=7 and course with ID=1) p.e. qAppendClasses:
INSERT INTO tblYourTableName (ClassNumber, SmsID, CrsID)
SELECT Class, 7, 1


FROM tblTempClasses

NB! Never include autonumber into INSERT query!

Run the queryqAppendClasses - the classes from temporary table are added into your table. If you created the unique composite index for fields ClassNumber, SmsID and CrsID, the query works when no new entry is copy of some previous, otherwise it returns an error (If you don't have such index, there will be a risk that you enter the batch of classes several times, unless you add a WHERE clause to check for double entries into append query). When the query was successful, run the query qClearTempClasses - the temporary table will be empty for next batch of classes. Repeat the cycle until the table is filled

[orange]

Re: your PM

You may get some ideas from these free data models from http://databaseanswers.org/data_models/index.htm

see this for high level conceptual model approach

see the individual data models for the various areas of interest.

http://databaseanswers.org/data_mode...oans/index.htm
http://databaseanswers.org/data_mode...mple/index.htm
http://databaseanswers.org/data_mode...plex/index.htm
http://databaseanswers.org/data_mode...ling/index.htm

tutorial on database schema based on the databaseanswers.org site

Good luck with your project.

[sarek007]

Attached is a made up version of the Excel spreadsheet I'm try to convert into Access. This format is as it was when I got it, since then I have split the cells so they each contain one attribute, ie, lastname, firstname. I've also split the courses and classes into separate cells.
Thanks anyone for any help, you guys have been great already, it's me being slow!

[orange]

Can you post the excel sheet, not a graphic.

As discussed, I have attached a demo db in zip format.

It is not intended as an application. It was a personal database to review table data macros.

[ArviLaanemets]

Even with only graphic presented, there arouse some questions and guessings.

Questions:
1. What is Student ID? Some ID given to student in your Excel table, or some official ID read from some document and entered into your table?;
2. What is Site? A place the student origins from, or a place he/she studies?;
3. What is Active Duty? Is it attached to student, to class, or to some other field? Is it fixed value from start of studies until to end of them, or will it change over time?;
4. What is VA Chapter? Same questions as in p.4;
5. What is VA Grad? Same questions as in p.4;
6. Courses can be Major or UCC? Is this distinction made by course, or by course taken by student?

Guesses:
1. Credit Hours are hours the student can participate in classes he/she is registered into? Can the student take additional hours later?;
2. Charges is amount in $ the student must pay for Credit Hrs? Is there some price ($/hrs) determined? (overall/per course/ at year/etc.);
3. Amount Certified and Date of Cert are data for some operation/decision made with charges?;
4. Hrs Verified is a date when is registered, that the student did participate in class(es) for all registered hours? What about the case, the student could'nt participate for all hours? Are hours verified for class, course, semester or year?;
5. Logged by is probably some official entering some data? For charges, or for payments, or for what?

(Almost) sure (when field in table structure is between brackets, it's optional):
1. You'll have a table tblStudents: StdID (autobumber, needed in case you dont use StudentID as PK), StudentID, ForeName, LastName, SiteID (in case the Site is attached to student);
2. You'll have a table tblSites: SiteID, Site;
3. You'll have a table tblSemesters, SmsID, YearNo, Period ('Fall', 'Winter', 'Spring') ;
4. Youll have a table tblCourses: CourseID, SmsID, (YearNo,) (Period), CourseNo, SiteID (in case the Site is attached to course), (CourseType - Major or UCC in case the type is determined for course), (CourseInfo), ...;
5. You'll have table tblClasses: ClsID, CourseID, (YearNo,) (Period), (CourseNo), ClassNo, (ClassInfo), ...;
6. You'll have a table tblStudentClasses: StdClsID, StdID, ClsID, (CourseType - Major or UCC in case the type is determined for class), ...
7. You'll have table tblStudentCharges: StdChrID, StsClsID, (StdID), (ClsID), (CourseID), (YearNo,) (Period), (CourseNo), CreditHrs, HrsVerified (?);
8. You'll have some table where real payments from student are registered.

[sarek007]

Sorry Orange, I don't know how to just send the sheet with fake data?