Query based on monthly updates

[Guest]

I have a table for ITYPES, they are brief discriptors of collections in our
library. Every month I have a report on how many circulations occurred for
each ITYPE. The report include a field for ITYPE and Circount. Now there are
months that some ITYPES have no circs and won't appear on the report. I have
set up a relationship between the ITYPE int the Itype table and the ITYPEs in
the monthly reports. BUT.
When I run a query with more than one month I get incomplete results. So far
I just had two months of reports and the ITYPE appears in the results only if
it has circulated in both months. I would like results that have the ITYPES
in the first column and it's circ count in the varioius month colums.

Lin

 

[Guest]

Lin:

To include rows from the Itypes table in the query's result set where there
are no matches in the table of circulations you'll need to use an outer join.
To show the months as column headings you should make the query a crosstab
query. To show all months in the correct order regardless of whether they
have any circulations, the query should include an IN clause. You don't say
how the circulations are recorded in the other table, but if there is one row
per circulation with a column for the date of circulation you would use the
COUNT function to count the rows per Itype per month. So assuming the tables
are called Itypes and Circulations, the date column is called CircDate, and
the join column is called Itype a query for all this year would go like this:

TRANSFORM COUNT(Circulations.Itype) AS CirculationCount
SELECT Itypes.Itype
FROM Itypes LEFT JOIN Circulations ON Itypes.Itype = Circulations.Itype
GROUP BY Itypes.Itype
PIVOT FORMAT(CircDate,"mmm yyyy")
IN ("Jan 2006", "Feb 2006", "Mar 2006","Apr 2006","May 2006","Jun 2006","Jul
2006","Aug 2006","Sep 2006","Oct 2006","Nov 2006","Dec 2006");

If the Circulations table records the number of circulations as a value in a
column in the table rather than having one row for each circulation, you
could use SUM(Circulations.CirCount) AS CirculationCount instead of the COUNT
function.

Ken Sheridan
Stafford, England

 

[Guest]

Each table will have ITYPES (Text) and totcircb (Number) for fields.
Totcircb will have a number at the end to represent the month, so totcircb8
would mean August. Also the "b" stands for Branch. We have two libraries. So
I will have a table for HNB (our branch) Aug Circ and HNB Jul Circ. Then I
have the table for ITYPES which just has the field itypes. So for Adult
Fiction Itype I will have a value of say 25,000 for Aug and 33,000 for Jul.
Michigan Collection may have Zero for both months and I would not need to
have it appear in the results.

Does that information help.

I did a crosstab but I still get the ITYPES with zero circs showing up


Lin

 

[Guest]

Lin:

Your problems stem from the fundamental flaws in the design of the database,
I'm afraid. What you are doing is what's known in the jargon of the database
relational model as 'encoding data as column headings', e.g. using a number
in the column name to indicate the month. A fundamental principal of the
relational model is that data should be stored as values at column positions
in rows in tables, and in no other way.

A correct model would be to have two main tables, Itypes and Circulations.
The Itypes table is simply a list f all the Itypes in it Itype column (I'm
using the more correct terms rows and columns rather than records and
fields). The Itype column would be the primary key.

The Circulations table would have columns Itypes (a foreign key referencing
the primary key of the Itypes table), CircYear (so you can distinguish
between the same months in each year), CircMonth (you can use a number or
text for this as you prefer), Branch, CircTotal. For each month you simply
enter the year, month, branch name and the total circulations.

To complete the model you should also have a Branches table with a Branch
column as its primary key which is referenced by the Branch foreign key
column in Circulations. The Branches table would have just two rows of
course. By enforcing referential integrity between this table and
Circulations only the correct branch names can be entered in the latter.

To return the figures for July and August 2006 for your branch for instance
would then be simply a matter of joining the tables and restricting the
results to the appropriate branch, year and months, e.g. if you use numbers
for the CircMonth:

SELECT Itypes.Itype, CircMonth, CircTotal
FROM Itypes INNER JOIN Circulations
ON Itypes.Itype = Circulations.Itype
WHERE CircYear = 2006
AND CircMonth IN (7, 8)
AND Branch = "HNB";

You could create a crosstab query based on this query to have the months as
column headings, or you could base a report on the query and arrange the
report like so:

1. Group the report by Itype and give it a group footer.

2. In the group footer put a text box bound to the Itype column and two
unbound text boxes alongside it with the following ControlSource properties:

=Sum([CircTotal] * IIf([CircMonth] = 7,1,0))
=Sum([CircTotal] * IIf([CircMonth] = 8,1,0))

3. The first text box will show the July figure, the second the August
figure, so put a label for July, August as appropriate above each.

4. Reduce the detail section to zero height.

Ken Sheridan
Stafford, England

 

[Guest]

Lin:

Your problems stem from the fundamental flaws in the design of the database,
I'm afraid. What you are doing is what's known in the jargon of the database
relational model as 'encoding data as column headings', e.g. using a number
in the column name to indicate the month. A fundamental principal of the
relational model is that data should be stored as values at column positions
in rows in tables, and in no other way.

A correct model would be to have two main tables, Itypes and Circulations.
The Itypes table is simply a list f all the Itypes in it Itype column (I'm
using the more correct terms rows and columns rather than records and
fields). The Itype column would be the primary key.

The Circulations table would have columns Itypes (a foreign key referencing
the primary key of the Itypes table), CircYear (so you can distinguish
between the same months in each year), CircMonth (you can use a number or
text for this as you prefer), Branch, CircTotal. For each month you simply
enter the year, month, branch name and the total circulations.

To complete the model you should also have a Branches table with a Branch
column as its primary key which is referenced by the Branch foreign key
column in Circulations. The Branches table would have just two rows of
course. By enforcing referential integrity between this table and
Circulations only the correct branch names can be entered in the latter.

To return the figures for July and August 2006 for your branch for instance
would then be simply a matter of joining the tables and restricting the
results to the appropriate branch, year and months, e.g. if you use numbers
for the CircMonth:

SELECT Itypes.Itype, CircMonth, CircTotal
FROM Itypes INNER JOIN Circulations
ON Itypes.Itype = Circulations.Itype
WHERE CircYear = 2006
AND CircMonth IN (7, 8)
AND Branch = "HNB";

You could create a crosstab query based on this query to have the months as
column headings, or you could base a report on the query and arrange the
report like so:

1. Group the report by Itype and give it a group footer.

2. In the group footer put a text box bound to the Itype column and two
unbound text boxes alongside it with the following ControlSource properties:

=Sum([CircTotal] * IIf([CircMonth] = 7,1,0))
=Sum([CircTotal] * IIf([CircMonth] = 8,1,0))

3. The first text box will show the July figure, the second the August
figure, so put a label for July, August as appropriate above each.

4. Reduce the detail section to zero height.

Ken Sheridan
Stafford, England

Each table will have ITYPES (Text) and totcircb (Number) for fields.
Totcircb will have a number at the end to represent the month, so totcircb8
would mean August. Also the "b" stands for Branch. We have two libraries. So
I will have a table for HNB (our branch) Aug Circ and HNB Jul Circ. Then I
have the table for ITYPES which just has the field itypes. So for Adult
Fiction Itype I will have a value of say 25,000 for Aug and 33,000 for Jul.
Michigan Collection may have Zero for both months and I would not need to
have it appear in the results.

Does that information help.

I did a crosstab but I still get the ITYPES with zero circs showing up


Lin