Access Query

Posted on 2013-09-15
Medium Priority
Last Modified: 2013-12-02
The purpose of the report is to determine what progress has been made from month to month. The steps for each client are Identify, Qualify, and Pursue. The report should be automatically able to pull the current status from the current month as well as the status from the previous month. My tables includes the below columns. What would be the best approach to create the query to build the reports attached.

-Status (dropdown: Identify, Qualify, Pursue)
-Identify Start Date
-Identify Completion Date
-Qualify Start Date
-Qualify Completion Date
-Pursue Start Date
-Pursue Completion Date
-Next status Completion Date

i.e. Say I have client ABC and it has moved from identify to qualify I will mark identify as completed with completion date 8/15. It should be similar to an IF statement if task is marked as complete then the date would be under Month of August.
Question by:doubleds
LVL 25

Expert Comment

ID: 39495055
Can you export the table to an empty Access database with some sample data and attach it here. That way it will be easier to generate the queries
LVL 49

Expert Comment

ID: 39495058
is it one table or more than one?

could you align the columns to each table if more than one please.
and, identify a column in each table that would allow them to join
e.g. "studentID"

sample data would also help answer quickly.

can I also suggest you use the actual table and field names, this means you don't have to "translate" generic replies to match your situation.
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 39496935

Yes, your table design there does not lend itself to standard Access reporting,
It seems "normalized", for example you have a record and you select the "Status", ...lets say you select "Qualify",...
Now, you still also have "Pursue" and "Identify" date columns for this record?

Something like this would be more standard:
...Then you could filter the report by Current/Previous Month.

The way you have it now, you must calculate the Current/Previous Month for each date column.

Yes, you can do it with your current design, but it involves a lot of lookups that may make the report run slow if you have a great many records...


Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question