如何只查看MS Access中的第一个值和空白？

How can i convert table 1 to table 2 in MS Access? Table 1 is a regular table In Table 2, sales_unit name is present only for the first entry, rest are blank.

如何在MS Access中将表1转换为表2?表1是常规表在表2中,sales_unit名称仅出现在第一个条目中,其余为空白。

From

agency  sales_unit  sales_rep
A4  ST7 Rep31
A4  ST7 Rep32
A4  ST7 Rep33
A4  ST7 Rep34
A4  ST7 Rep35
A4  ST8 Rep36
A4  ST8 Rep37
A4  ST8 Rep38
A4  ST8 Rep39
A4  ST8 Rep40

agency  sales_unit  sales_rep
A4  ST7 Rep31
A4      Rep32
A4      Rep33
A4      Rep34
A4      Rep35
A4  ST8 Rep36
A4      Rep37
A4      Rep38
A4      Rep39
A4      Rep40

2 个解决方案

#1

If its in a report, you can do it in one of two ways.

如果它在报告中,您可以通过两种方式之一来完成。

Use grouping and move the text box into the grouped header (e.g. you would say group on sales_unit and then move the sales unit text box into the sales_unit header section).

使用分组并将文本框移动到分组的标题中(例如,您可以说sales_unit上的分组,然后将销售单位文本框移动到sales_unit标题部分)。
Set HideDuplicates to yes in the property sheet of a text box when looking at a report in design view.

在设计视图中查看报表时,在文本框的属性表中将HideDuplicates设置为yes。

#2

I solved it. This is how it can be done. Note: full_data is the table containing the entire data but the method is applicable generally.

我解决了这是如何做到的。注意:full_data是包含整个数据的表,但该方法通常适用。

SELECT t1.agency, t2.sales_unit, t1.sales_rep
FROM 
(SELECT agency, sales_unit, sales_rep FROM full_data WHERE agency='A4' GROUP BY agency, sales_unit, sales_rep ORDER BY agency, sales_unit, sales_rep) As t1 
 LEFT JOIN 
(SELECT agency, sales_unit, First(sales_rep) As FirstOfsales_rep FROM full_data WHERE agency='A4' GROUP BY agency, sales_unit) As t2 
 ON (t1.sales_rep = t2.FirstOfsales_rep) AND (t1.sales_unit = t2.sales_unit);

#1