Where to Put Instructor Salary?

[Rainlover]

A Report with a sub report uses two queries.



I think you can use the same for your mail merge, but I have not played with Mail merge in over 10 years.

You will need to test.

[alpinegroove]

I think that the crosstab is exactly what I was looking for. Thanks for everyone's help.

[RayMilhon]

A Crosstab query is essentially a limited pivot table. The problem with a crosstab query is that while you can have as many Row Headers as you need You are limited to 1 Value. So say for example you have a database for customers and orders. You want to do a cross tab that shows each customers orders by Month you can do the crosstab on the sales amount of the order or on the quantity of orders but not on both. You have to do each query separately. If anyone has a better explanation please let me know.

[Rainlover]

A crosstab query is a last resort.

It is usually used to overcome poor Table Structures.

I have used it to turn the Dates from (Top to Bottom) to (Left to Right). A correct usage.

But I see so many use it incorrectly to fix what should not require fixing.

Suggest you go back to Database design.

Remember this is a Database not a Spreadsheet.

Also starting a fresh Back End every year is another sign of poor design.

[alpinegroove]

A crosstab query is a last resort.

It is usually used to overcome poor Table Structures.

I have used it to turn the Dates from (Top to Bottom) to (Left to Right). A correct usage.

But I see so many use it incorrectly to fix what should not require fixing.

Suggest you go back to Database design.

Remember this is a Database not a Spreadsheet.

Also starting a fresh Back End every year is another sign of poor design.

We run a fairly basic operation here, and we are not experienced database designers, as should be clear by now. So we do what we can and come here for help.
You keep saying that I focus on database design. That is exactly why I came here for advice, so if you have suggestions, please share them.
If the crosstab query should be avoided in this case, what is the alternative?

[Rainlover]

I have already given advice in this regard, if you care to reread my posts.

A Start Date for wages/contract would be recommended.

So that you can continue with just the one Database over several Years you may need to introduse a Field Yes/No to mark the record as active or Inactive.

I would tend to think that more information could be stored in this Database. Like more personal details. More on the course. etc. You may wish to keep this in mind for future development.

I would like to see a copy of your Database posted here. Make sure it has no sensitive information.

Repeating Data is things like, eg Direction. North, South, East and West. If you had 100s of records then one of 4 directions would be used many times. But if a Direction was degrees Minutes etc then each value could be unique so in the latter case it would not be repeating data.

Cities, Postal Codes, States are another example of repeating data and should be stored in a separate Table.

I still do not understand your salary issue. Is it different for each situation or is it a standard rate for each item.

Do you know how to create a Form with a Sub Form, or a Report with a Sub Report. This is what I think could be used instead of a Crosstab.

You don't need a Mail Merge document if you were thinking of using Word. Simply use a Report from Access. Does the same thing only faster and with little user intervention.

Suggest you read up on Normalisation. It is the key to a good Database design.

Must go now. Santa is due in 30 Minutes and I haven't left out any cookies.

Merry Christmas.

[RayMilhon]

Just my .02 cents. First, I absolutely agree with RainLover. Crosstabs should be a last resort if nothing else works. Given your requirements it works. It is not a long term solution. It will give you something to use to get your data out while you learn proper database design and re-design your database properly.

[Rainlover]

Ray

Thanks for your words of support.

I feel that the OP has found a solution and will not go the extra mile and change. In some ways I can't blame them.

We just need to be prepared for when they come across the next problem.

[RayMilhon]

I have worked for manager's before that only care if a project is done first quickly and second correctly and by that I mean that the data returned is correct not that it's done in the correct manner. If the manner in which it is done will make future enhancements or modification difficult or impossible is irrelevant to them. Makes our jobs much harder but keeps us employed. Personally I've never been able to stand working for these type of employers for an extended period of time. So the longest job I've had in the last 10 years was 2.5 years at the end I was either going to find another employer or go postal. Luckily I found another employer. This is the first time in 10 years My supervisor actually rose through the ranks and understands exactly what we do and how we do it. I *MAY* survive here until retirement.

[alpinegroove]

Ray

Thanks for your words of support.

I feel that the OP has found a solution and will not go the extra mile and change. In some ways I can't blame them.

We just need to be prepared for when they come across the next problem.

I have been out of town and unable to respond. Please note that my coming to this forum is precisely taking the extra mile. My workplace already has a very basic database for this purpose, which I find inadequate. I thought it could be improved and took the initiative to redesign it. I am not trying to take any shortcuts, but given my lack of formal training in database design, I also need to be realistic and come up with solutions I can handle.
If a crosstab query is a last resort, I would like to learn how not to use it.
More details to follow.
Thanks for everyone's time and suggestions and Happy New Year.

[alpinegroove]

A Start Date for wages/contract would be recommended.
So that you can continue with just the one Database over several Years you may need to introduse a Field Yes/No to mark the record as active or Inactive.

I completely see how this could be useful, but for a number of reasons our business process does not require it.

I would tend to think that more information could be stored in this Database. Like more personal details. More on the course. etc.

You are correct. My tables already have this and other information in them. I only mentioned the ones relevant to my specific question.

I would like to see a copy of your Database posted here. Make sure it has no sensitive information.

I will post a copy of the database on Monday.

I still do not understand your salary issue. Is it different for each situation or is it a standard rate for each item.

It is not standard. There are certain loose guidelines, but it does vary from person to person and/or course to course.

Do you know how to create a Form with a Sub Form, or a Report with a Sub Report. This is what I think could be used instead of a Crosstab.

I know how to create a Form and a Sub Form. In fact, I already created one in this database to allow data entry given the junction table. The form is based on tblCourses and the Sub Form on tblCoursePeopleRoles.

You don't need a Mail Merge document if you were thinking of using Word. Simply use a Report from Access. Does the same thing only faster and with little user intervention.

The reason I was thinking of using Mail Merge and Word is that I need to be able to e-mail pdf copies of the contract to each instructional staff and keep a copy for my records. The contract is five pages long and data needs to be inserted on different pages. I might in some cases need to make manual changes to contracts. How do I use a report in Access to achieve all of this?

I will upload the database on Monday. Maybe my problem will be more apparent then.

Thanks again for taking the time to help out. It is greatly appreciated.

[Rainlover]

The reason I was thinking of using Mail Merge and Word is that I need to be able to e-mail pdf copies of the contract to each instructional staff and keep a copy for my records. The contract is five pages long and data needs to be inserted on different pages. I might in some cases need to make manual changes to contracts. How do I use a report in Access to achieve all of this?

I have never done this before but I am sure with the help of others we could do all this at the click of a button.

That is, Merge, Print to PDF and then Email attachment.

When we get to this stage it may be better to start a new thread.

Look forward to seeing a Copy of the Database.

[alpinegroove]

I have already put together some VBA code to automate the mail merge with a click of a button. It took a lot of research and trial and error. However, as far as I can tell, the only way so far to accomplish this is using a crosstab query that organizes the data in a way the mail merge can understand it.

[Rainlover]

That may very well be the case.

If you are prepared to post a cut down version of your Database then that would be good.

[alpinegroove]

I don't have a copy of it where I am right now, but I will post it as soon as possible.