Combining Two fields into One with a Query using Access 2007

[REAbernathy]

This is my problem...I have a very simple query SELECT Tigers.[Error Code], Tigers.[Error Description] FROM Tigers; and it runs just fine however what I am trying to do is combine these two fields into one. All this information is in the same database. Thanking You in advance for your assistance in this matter.

[R_Badger]

Try something along the lines of:

SELECT Tigers.[Error code], Tigers.[Error Description] (Tigers.[Error code] & " " & Tigers.[Error description]) as ErrCodeDesc FROM Tigers

You can find a bit more on alias here

Also as a sidenote, you should try to avoid using spaces in names and try to use an underscore instead e.g. Error_Code instead of Error Code.

I also highly recommend using a naming system for your system, it doesn't matter what (I use my own variant to make it easier to read albeit slightly slower to type.

[REAbernathy]

Thanks so much that helps so much however how do you remove duplicate records from the query?

[R_Badger]

You need to use the SQL distinct see here

[REAbernathy]

SELECT Distinct [Firms Information].[JDG CODE] &" " & [FIRM NAME] AS [Customer Name], [Firms Information].ID
FROM [Firms Information];

I tried this and it didnt work it still gave me duplicate records for the Firm Name.

[orange]

What makes you think there are duplicates? Show us some data.

It is this combination [Firms Information].[JDG CODE] &" " & [FIRM NAME] AS [Customer Name], [Firms Information].ID that is DISTINCT.

[REAbernathy]

SELECT DISTINCT [JDG CODE] & " " & [FIRM NAME] AS [Customer Name], [Firms Information].ID
FROM [Firms Information];

Customer Name	ID
102 Dang (Marvin S.C. Dang, L.L.L.C.)	28
102 Dang (Marvin S.C. Dang, L.L.L.C.)	29
104 Paris and Paris	91
114 Sawaya & Rose, P.C.	49
114 Sawaya & Rose, P.C.	50
114 Sawaya & Rose, P.C.	51
115 Hollins & Schechter	89
115 Hollins & Schechter	90
116 Patenaude & Flex, APC	43
116 Patenaude & Flex, APC	44
120 Creditors Interchange	8
121 Wright & Lerch	62
121 Wright & Lerch	63
121 Wright & Lerch	64
121 Wright & Lerch	65
121 Wright & Lerch	66
121 Wright & Lerch	67
123 Johnson Mark, LLC	46
123 Johnson Mark, LLC	47
123 Johnson Mark, LLC	48

[orange]

Right no duplicates, just as the sql suggests.

What exactly do you want to see?
What are you trying to do?

[REAbernathy]

OOPPPSSSSSS.....I am loud and wrong you are correct there are no duplicates. I warned you guys I am a newbie to Access after ten years of not playing with it at all. So again please forgive Orange.....

Can you help me with another issue that I am having? On my form I have 2 command buttons. One of the command buttons is suppose allow me to Preview Reports which is suppose to show the current record. The other command button is the Mail Report which is suppose to let me e-mail the current record that we just viewed. I have some code that i will let you review and can you please tell me if I am on the right path.

Here is the code for Preview Reports
Private Sub Preview_Reports95_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "TrackingSystemReport3"
strWhere = "[ID]=" & Me!ID
DoCmd.OpenReport strDocName, acViewPreview, strWhere
End Sub


Here is the code for Mail Report

Private Sub Mail_Report_Click()
On Error GoTo Err_Mail_Report_Click

Dim stDocName As String
Dim stEmail As String
Dim stSubject As String

stDocName = "TrackingSystemReport3"
stEmail = "my@email.com.au"
stSubject = "TrackingSystemReport3 & Me!Time & ", " & Me![Date]

DoCmd.SendObject acSendForm, stDocName, acFormatSNP, stEmail, , , stSubject, , , False

Exit_Mail_Report_Click:
Exit Sub

Err_Command48_Click:
MsgBox Err.Description
Resume Exit_Mail_Report_Click

End Sub

End Sub

And again Thank You so much....

[orange]

I think you're on the right track.

Here is a code snippet from a data base where there were options for the output
(form, reportPreview, ReportNormal)

I think you are missing a comma in your OpenReport line (see the code snippet).
I believe your where clause is in the Filter position.

'** Output the results to the proper destination
Select Case Me.CboOutput.Value
    Case "Form"
     DoCmd.OpenForm "FoundItems", acFormDS, , whereClause
    Case "Report - Preview"
        Debug.Print "Report - Preview"
        DoCmd.OpenReport "rptMeep", acViewPreview, , whereClause
    Case "Report - Print"
        Debug.Print "Report - Print"
        DoCmd.OpenReport "rptMeep", acViewNormal, , whereClause
    Case Else
End Select