Excel Type Table

[gheyman]

I have an excel "Table" I want to recreate in Access. My table has Departments down Column A, Year and Date/Month in Rows 1 & 2. In-between these is where my data goes in for each department and each Month. Can you do thins in Access?

Also I use a Sumproduct formula, in excel, to retrieve data by Department & Year. Is there a way to query data the same way in Access?

Thanks

 

[welshgasman]

I cannot envisage your table, so cannot answer that question.

As to your second question you would use a Totals query where you would group by Dept and Year and anything else you would want.

HTH

 

[gheyman]

Thanks. Good to know that I can group and total. The big question was the layout. Ill try again;

In Excel Down Column A I have Departments: Quality(a2), Assembly(a3)... Across the top of the spreadsheet in Row 1 I have 2017 (b1), 2017(c2), 2018(d2)... In Row two I have 11/1/2017(b2), 12/1/2017(c2), 1/1/2018(d2).... So if the user worked 200 hours in Quality for the Month of November 2017 Cell B3 would have 200 in it.

(I am referencing Cells next to the example Titles: (a2) means Cell A2 n the Excel form)

 

[welshgasman]

I *think* that would be a Pivot table?

https://support.office.com/en-gb/ar...0a48c1aa6752?ui=en-US&rs=en-GB&ad=GB&fromAR=1

 

[HiTechCoach]

Access should have no problem handling the data.

Your Excel data is currently laid out how Access can present the data in a report. It in NOT how you would store the data in table in Access.

In any relational database, including Access, the data is stored based on a model of the data. Not how you want to enter the data or print the data. This aloes the data top be easily reformatted with queries and reports to display the data in many formats.

I generate reports like you described using a report based on a cross-tab query.