setting up relationship

[chrisco]

I am wanting to print a report using Access 2K, VB6.0 and Data Reports as follows...

January
Jack 1/1/00
Dave 1/14/00
Phil 1/22/00

February
Steve 2/2/00
Chris 2/18/00
Donna 2/20/00
Cindy 2/30/00

I'm not 100% sure if this is correct but....

I assume I need a table w/ one instance of the Month

Jan
Feb
Mar
Apr ...and so on

But I need to attach multiple instances of employees as follows...

Jan Jack Dave Phil
Feb Donna Cindy Chris Steve

I have 2 tables but I don't know of any way to set up a relationship between the 2 so this can occur. Does anyone know how I can do this w/o having up update 2 non-related tables when adding new employees to the database?

 

[huzza]

Hi,
you should be able to get away with one table that has the columns
employee, dateWorked, monthWorked
(note you can't use date and month as column names as they are functions in access)
you can then design your report to group by month and put the month in the group header and the employee and date in the detail section.
To save typing on behalf of whoever enters the data you would probably want to automate the filling in of the month column based on what is put into the date column. for example:

 MonthWorked = Format(Month(DateWorked), "mmm")

Then mmm tells access to display Jan, Feb etc, to display full month names change it to mmmm
I hope this helps,
Richard

 

[chrisco]

Where do I put the code....MonthWorked = Format(Month(DateWorked), "mmm&quot

Thanks for your help

 

[huzza]

Hi,
sorry about that. Would I be correct in assuming you update the tables using a form?
If so then you can either:
1)Set the AfterUpdate event of the control that the date worked is entered into to fill in the month worked control on the form (so the user can see that it's filled in correctly).
In this case you would want the event to read

Private Sub DateWorked_AfterUpdate()
Me.MonthWorked.value = Format(Month(me.DateWorked.value), "mmm")
End Sub

If the MonthWorked control is bound like the others to the talbe it will update the table at the same time as the others.

2)If you have an update button that you use to save the data that's been entered into the table then as part of this button's code you could get it to calculate this then run an SQL string to update the table with this value.

If you need any more help let me know,
Richard.

 

[chrisco]

I am using a DataGrid to display the dbase records and I have an ADODC control linked to that.

If the user wants to add a new employee to the record they can either enter them into the datagrid or use text boxes provided. They then click save and it confirms the record has been added.