在Microsoft Access中如何实现交叉列布局的患者统计报表？

You’re already off to a great start with that deduplication and count query—perfect for ensuring each patient is only counted once per provider per day. Access has a built-in tool made exactly for this kind of row-to-column pivot layout: Crosstab Queries. Since you mentioned your SQL skills are still developing, I’ll walk you through both the easy wizard-based method and a simplified SQL example.

Method 1: Use the Crosstab Query Wizard (No Fancy SQL Needed) #

This is the most straightforward way to get your desired layout:

• Go to the Create tab in Access, click Query Wizard, then select Crosstab Query Wizard and hit OK.
• When asked for a data source, choose the query you already wrote (the one with SELECT DISTINCT Patient, Day, Provider FROM Records nested inside) — this ensures we’re working with deduplicated patient counts right from the start.
• For Row Headings, select Provider (this will make each provider a row in your final result).
• For Column Headings, select Day (Access will automatically turn every unique date into its own column).
• For Values, select Patient and choose Count as the aggregate function (this calculates how many unique patients each provider saw per day).
• Name your query (e.g., ProviderDailyPatientCounts) and click Finish. Run it, and you’ll see exactly the layout you described: providers down the left, dates across the top, and patient counts in each cell.

Method 2: Manual Crosstab SQL (If You Want to Try It) #

If you’re curious about the SQL behind it, here’s a simplified version that builds on your existing query:

TRANSFORM Count(Patient) AS DailyPatientCount
SELECT Provider
FROM (
    SELECT DISTINCT Patient, Day, Provider 
    FROM Records
) AS DeduplicatedRecords
GROUP BY Provider
PIVOT Day;

• TRANSFORM tells Access we want to pivot data and specifies the aggregate function (counting patients here).
• SELECT Provider sets our row groups.
• The nested subquery is your original deduplication logic, ensuring we don’t count the same patient twice for a provider on the same day.
• PIVOT Day turns each unique date into a column.

Using This in Your Report #

Once you have the crosstab query working, creating your report is easy:

• Go to the Create tab, click Report Wizard, select your crosstab query as the data source, and follow the prompts to format your report. Access will automatically map the pivot columns (dates) and rows (providers) into a clean, printable layout.

Best part? If you add new dates to your Records table later, the crosstab query will automatically add those new date columns the next time you run it—no manual updates needed.

内容的提问来源于stack exchange，提问作者YoungTommy