Interesting Dilema

[CementCarver]

I have a client who tracks submissions from customers on a weekly basis. They're using excel right now and just add a check mark under one of the 52x columns to indicate whether the customer has sent in their submission. At times throughout the year, the customer will not submit their data, so there's a gap in the submission dates, but it is these gaps that they are interested in.

I'm trying to come up with an alternate solution. I was contemplating making a chart with the weeks of the year on the x-axis and create a chart of each submission date, but I need to show these gaps too.

Does anyone have any good ideas on how I can store this data, or if a chart is the best way to go? I've looked up time charts, but it does not sound like Access 2010 has good charting capabilities.

Any ideas would be appreciated.....CementCarver

[rpeare]

You're talking about exception reporting.

Are you looking to import their excel sheet into MS access and do your reporting there, or are you looking to convert the entire application to MS access? How you approach it really determines your method and one is vastly different than the other.

[orange]

I think it requires stepping back from Excel and Access for a moment and identifying the real issue.
It seems to me to be a weak (but I haven't seen any description or list) set of facts or specifications.

In a business sense, if a Customer submits something, the Customer typically gets some sort of receipt/acknowledgement that this ZZ was received from X on Date Y. It seems that a check mark in a column could be mistyped, or adjusted before other processing, without anyone being aware, or having any recourse.

It certainly is an issue, but it is fundamental in my view. That is, not trivial, and not to be overlooked.

Perhaps I'm taking it way to seriously, but we haven't heard the severity nor the real requirements of the whole process.

[CementCarver]

As far as I understand, the existing columns in excel are strictly a visual display of when a customer has submitted something. There's no receipts, but rather just an indication that for that particular week, that the customer gave notice to my client.

This existing process will be transformed into Access, and all I was trying to accomplish by posting this question, was to gather ideas of a visual display with only the 52x weeks as a base to work from.

There's no other data to place in a y column of a chart, if I was to use such a method of display.

I was initially looking at time charts, or a pivot chart of some sort, but I would need to be able to show a gap of when the customer, for some particular week, did not submit anything. Here's an example.... for the month of Jan/13...week 1 has a check mark, week two has a check mark, week three and four are blank. Then for the first week of Feb/13 it has a check mark etc. It goes on like this for the entire year.

Right now, I have no specs to offer anyone, no table designs or relationships, but just an existing visual display that my client has asked me to provide a solution for.


CementCarver

[rpeare]

I wouldn't try to make this a matrix.

I would have:

1. A table for customers with a PK
2. A table for files received with a PK, a FK to the customer table and a date received


If you are interested in a week number (1 through 52) you can get that with a combination of the datediff, datepart, int and dividing by 7

int(datediff("d", filedate, cdate("1/1/" & datepart("yyyy", filedate)))/7)

then all you'd need is a table with the numbers 1 through 52 in a column, use all the records in that table that DO NOT have a match in the query you build with this calculated value. those would be your exceptions.

way easier than creating a x52 matrix that you'll have to manipulate.

[CementCarver]

Rpeare,

Thx for your input. Seems like a solution, but don't fully understand your suggestion. Iget the first part of your suggestion about the two tables and extracting the week number, but I don't get the rest of it. Can you please explain with more detail?

CementCarver

[rpeare]

I'm enclosing a very stripped down, simple example.

CementCarver.zip

[CementCarver]

You're very kind rpeare and I appreciate your assistance. Thanks again.