IIF Conditional formatting (Bold), and how to combine rows? (1 Viewer)

[kb44]

Hi all,

I have a query that runs fine but needs tweaking. It displays differences between an old file and a new file. I haven't been able to figure out how to do two things. Here is one entry:

BU_Changed: IIf([Staffing_Differences_New_vs_Old].[Changed_BU_Y_N]=1,[Monthly_Staffing_No OPEN_or_Blanc_ID].[Business Unit],"")

1) I would like the following result to be displayed in Bold:

[Monthly_Staffing_No OPEN_or_Blanc_ID].[Business Unit]

2) Right now results are displayed in a table and each result is in a separate row. So, 6 rows are showing up for each one. I would like the results to be displayed in one row for each ID.

Here is the SQL:

SELECT [Monthly_Staffing_No OPEN_or_Blanc_ID].[ID], IIf([Staffing_Differences_New_vs_Old].[Changed_Country_Y_N]=1,"<b>" & [Monthly_Staffing_No OPEN_or_Blanc_ID].[Country],"") AS Country_Changed, IIf([Staffing_Differences_New_vs_Old].[Changed_BU_Y_N]=1,[Monthly_Staffing_No OPEN_or_Blanc_ID].[Business Unit],"") AS BU_Changed, IIf([Staffing_Differences_New_vs_Old].[Changed_Corp_Job_Y_N]=1,[Monthly_Staffing_No OPEN_or_Blanc_ID].[Corporate Job Title],"") AS Corporate_Job_Changed, IIf([Staffing_Differences_New_vs_Old].[Changed_Reports_To_Y_N]=1,[Monthly_Staffing_No OPEN_or_Blanc_ID].[Reports To],"") AS Reports_To_Changed, IIf([Staffing_Differences_New_vs_Old].[Changed_Territory_Y_N]=1,[Monthly_Staffing_No OPEN_or_Blanc_ID].[Territory],"") AS Territory_Changed INTO Staffing_Changed_Fields_NO_OPEN
FROM [Monthly_Staffing Data], Staffing_Differences_New_vs_Old, [Monthly_Staffing_No OPEN_or_Blanc_ID];

 

[plog]

1. Not applicable in queries. You can conditionally format in Reports though.

2. Most likely you will need to use a GROUP BY in your query. In Design view click the Sigma symbol (aka Summation symbol) in the tool bar. This will GROUP By every field in your query.

If that doesn't work, you will need to demonstrate your issue with data. We would need 2 sets:

A. Starting sample data from your table. Include table and field names and enough data to cover all cases.

B. Expected results from A. Show us what data you expect to ultimately end up with when you feed in the data from A.

 

[MajP]

To put multiple records into a single row you may consider
http://allenbrowne.com/func-concat.html

 

[kb44]

Here is the current and expected output. I had to redact info because some of it is sensitive.

 

[plog]

Nope, don't care about erroneous attempts you've made, nor did you include table names. Try again:

A. Starting sample data from your table. Include table and field names and enough data to cover all cases.

B. Expected results from A. Show us what data you expect to ultimately end up with when you feed in the data from A.

 

[MajP]

Looks to me you just need a group by query and take the max of each field.

 

[kb44]

To put multiple records into a single row you may consider
http://allenbrowne.com/func-concat.html

Thanks but not what I was trying to achieve. I won't have repeating info from each row.

 

[MajP]

SELECT 
 ID, Max(Country_Changed) AS CC, Max(BU_Changed) AS BU,...
FROM 
 YourTable
GROUP BY ID;

 

[kb44]

Guys,

So, no Bold. I actually didnt need it after all. Also, as far as the data that was not combined, it was because of some typos from the table name references. So it all works now.

Thanks!