Query to produce Items to Action Table

[Chris Waller]

Hello,

I have created an Access Database which has replaced 15 Spreadsheets. The problem apart from me being a novice at Access, is that I don't know how to create the table below which has been copied from Excel. I have two columns that hold dates. The first column is the date the item was received by the team and the other column is the date that action was completed. The table has to be amendable for any month\year and any number of months etc.

The data on the BF row calculates anything that was received by the team before the first day of the current month and has no date in the completed column.
The data on the new row calculates anything that was received within the specific month\year.
The data on the Carried Forward row is to all intents and purposes the same as the BF data.

As I am a novice at Access, please be gentle with me.

	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14
BF	2	8	9
New	16	22	9
Carried forward	8	9	13

[ranman256]

This may be good for XL but not for databases.
Your acutal table fields should be

[code], [Date], [Value]
BF , Jun-14, 8


This allows searching, other functions. The final XL layout like you have above can be reproduced in reporting/queries, but it is useless for data searching.
Adjust your table accordingly.

[orange]

Chris,

You need to work through a tutorial or two to understand what database and Access really are. You are in spreadsheet mode and that just does not apply to database, as ranman256 has said.
Here is a tutorial with a solution that will help you understand database/design.
You have to work through the tutorial. Good luck.

[Chris Waller]

Thanks for the replies, I will have to work through the tutorial I guess. Thanks again.

[Chris Waller]

Thanks for that. I was only ever interested in running a report to show the current position rather than trying to do any searches or anything fancy etc.