Create report from table with multiple data of the same type

[edwardcmorris]

I am trying to work out a statistics database, all my data goes to one table
(Statistics).
Within this table I have a date field and 5 columns that may contain one of
6 fruits and 5 Columns for Qty.
Eg. Column1 (Fruit Type) Column2 (Qty) Column3 (Fruit Type) Column4 (Qty)

How can i generate a report that will allow me to input a date range then
calculate the Combined total of Fruits counted within this date range from
all the columns even though the same fruit type may be listed in multiple
columns

 

[pietlinden]

I am trying to work out a statistics database, all my data goes to one table
(Statistics).
Within this table I have a date field and 5 columns that may contain one of
6 fruits and 5 Columns for Qty.
Eg. Column1 (Fruit Type) Column2 (Qty) Column3 (Fruit Type) Column4 (Qty)

How can i generate a report that will allow me to input a date range then
calculate the Combined total of Fruits counted within this date range from
all the columns even though the same fruit type may be listed in multiple
columns

you might want to rethink your design. The only way to summarize data
like this with SQL is to do a union query, and they're an absolute
last resort. They're obscenely slow, memory-intensive, cannot be
indexed... Is there a reason you have to have your data this way?

 

[John W. Vinson]

I am trying to work out a statistics database, all my data goes to one table
(Statistics).
Within this table I have a date field and 5 columns that may contain one of
6 fruits and 5 Columns for Qty.
Eg. Column1 (Fruit Type) Column2 (Qty) Column3 (Fruit Type) Column4 (Qty)

How can i generate a report that will allow me to input a date range then
calculate the Combined total of Fruits counted within this date range from
all the columns even though the same fruit type may be listed in multiple
columns

Well... this is a good spreadsheet design but a very bad relational design.
"Fields are expensive, records are cheap" - someday you'll need a *sixth*
fruit/quantity pair (pear?) and will then need to restructure your table, all
your queries, all your forms, etc., quite aside from the difficulty of dealing
with your statistics!

What other tables do you have, or what other information is in this table? Can
you consider a normalized structure, with a one to many relationship from a
table (containing a date presumably) to a table with a single field for fruit
type and for quantity?