2 records into 1 field?

[daveUK]

I'm currently designing a database for a univeristy reprographics department and am having a sloght problem.

I have 2 tables- TblStaff, which contains Autonumber, Surname, First_Name. TblUsage, which contains info about photocopier usage as well as Staff_Name (link to TblStaff)

I have a combo box on FrmUsage which displays the staff surname and firstname. The wizzard put this in 2 colums i.e. Surname | Firstname. I changed it with the following code, so everything appears as one column.

SELECT [TblStaff].[ID], [TblStaff].[Surname] & ", " & [TblStaff].[First_Name] AS Expr1 FROM TblStaff ORDER BY [TblStaff].[Surname] & ", " & [TblStaff].[First_Name];

This displays Surname,Firstname which looks a lot better.

On to the problem - After the user selects the staff surname and firstname only the surname appears in TblUsage. For reporting purposes, I really need to have both the Surname and Firstname appearing as many staff have the same surname i.e. Smith etc.

Is there anyway to do this, or will I need to have 2 fields in TblUsage, one for surname and one for firstname?

Thanks
Dave

 

[Mile-O]

Link tblUsage to StaffID and not the staff name.

 

[daveUK]

Thanks for the reply Mike, but it already is linked to StaffID. I've used a lookup wizzard in TblUsage to get the staff names. In the design of TblUsage the SQL code is

SELECT [TblStaff].[ID], [TblStaff].[Surname], [TblStaff].[First_Name] FROM TblStaff;

Bound Column =1
Column Count=3
Column Widths=0cm;2.54cm;2,54cm

Any ideas?

 

[Mile-O]

Just put the same SQL in your combo box into the table's field RowSource.

Where StaffID is the foreign key, set it's default value to Null (or just delete the 0)

SELECT [TblStaff].[ID], [TblStaff].[Surname] & ", " & [TblStaff].[First_Name] AS Expr1 FROM TblStaff ORDER BY [TblStaff].[Surname] & ", " & [TblStaff].[First_Name];

Set the column count to 2

Width: 0;2.54

 

[daveUK]

Thanks Mile-O-Phile, it's working now

Dave