Data Relationship Puzzle

[Richard Murphy]

Hey all,
I have a query that counts the number of items sold per month e.g.
Month CountofItemsSold
August, 2006 50
Semptember, 2006 45
October, 2006 60
November, 2006 47
December, 2006 52
January, 2007 38

In a separate table have a record of the number of days that the store
is open in each month per year (let's assume that the year goes from
August, 2006 - July, 2007 e.g.
Yearid Aug Sept Oct year
1 12 18 22 2006-2007
The number of days are static (entered at the beginning of the year)
while the number of items sold are dynamic.

I need to be able to divide the number of items sold by the number of
days in the month in order to get the average items sold per month. I
need the data to look like the chart below:
Year Month Dayscount Itemsoldcount Avgitemssold
2006 August 12 50 4.17
2006 September 18 45 2.5
2006 October 22 60 2.72

I can't figure out how to get the number of days in a specific month,
year to relate to the number of items sold in a specific month, year so
that I can do the math. Any suggestions would be MUCH appreciated. RM

 

[MBSNewbie]

I would assume by your information that you have one table with just Month,
Year, and # of Days Per Month
The other table(Or Crosstab Query) has Month, Year, And Number of Items
I would use the following: (SQL View)

SELECT Items.Year, Items.Month, Days.Days, Items.Items, [Items]/[Days] AS
AverageItemsSold
FROM Items INNER JOIN Days ON (Items.Year = Days.Year) AND (Items.Month =
Days.Month);

 

[pietlinden]

Hey all,
I have a query that counts the number of items sold per month e.g.
Month CountofItemsSold
August, 2006 50
Semptember, 2006 45
October, 2006 60
November, 2006 47
December, 2006 52
January, 2007 38

In a separate table have a record of the number of days that the store
is open in each month per year (let's assume that the year goes from
August, 2006 - July, 2007 e.g.
Yearid Aug Sept Oct year
1 12 18 22 2006-2007
The number of days are static (entered at the beginning of the year)
while the number of items sold are dynamic.

I need to be able to divide the number of items sold by the number of
days in the month in order to get the average items sold per month. I
need the data to look like the chart below:
Year Month Dayscount Itemsoldcount Avgitemssold
2006 August 12 50 4.17
2006 September 18 45 2.5
2006 October 22 60 2.72

I can't figure out how to get the number of days in a specific month,
year to relate to the number of items sold in a specific month, year so
that I can do the math. Any suggestions would be MUCH appreciated. RM

One way is to create a simple table of Month number and name and
include the number of days the store is open in the month. (I guess
you could do some quick date math to get the number of weekdays or
something.) Then you could store that in a table. Yes, you could calc
it on the fly, but what's the point, since it's not going to change.
Just prepopulate your table. Then join your summary query numbers
(totals per month) by the days open in the month by joining on the
Month/Year combination.

You should transpose the days open data so it's normalized/searchable.
In other words, instead of this structure:

August, 2006 - July, 2007 e.g.
Yearid Aug Sept Oct year
1 12 18 22 2006-2007

Use
Month | Year | Days Open
8 1 12
9 1 18
10 1 22

etc.

THen you can just query the info together in a query based on that
table and your totals. (just join on the calculated month and year.)

 

[Richard Murphy]

Thanks for the response,

I have the days per month in a record for each year e.g.
and I'd like to avoid having a separate record for each month as you
suggested

Use
Month | Year | Days Open
8 1 12
9 1 18
10 1 22

I see how this would make relating the data easier, but I'd like to be
able to store days per month in one record per year rather than 12
records per year. I've been trying to figure out how to pivot the row
so that the dates would stack chronogically like they do in the query
to which I'm trying to match them so that I can relate the year fields
and thus be able to divide items sold per month by days per month, but
that may not be possible. You may have given me the only solution that
is viable.