Put values from a query to a table

[Guest]

Hi All,

I am a new user to this discussion group. Hope you'll are having a good day.

I am currently trying to establish a database where I have a table with the
below display

eg:

Table :Rev
Item_ID Apr_Rev May_Rev
Item A $2 $3
Item B $3 $1
Item C $4 $12

I did a query to Sum up the items based on the months, I have values for all
12 months.
In my query now i have 12 fields that gives me sum values eg Sum_Apr_Rev,
Sum_May_Rev etc
I need to get that information and put into another table as below for all
the twelve months

Month Sum_Rev
Apr $9
May $16
..
..
..

I am not sure how i can do that, please help
Thank you

Joyce

 

[Jeff Boyce]

Whoa! Too many "tables"...

And too many fields. If you find yourself with one-field-per-month, you
have a spreadsheet, not a relational database table. Take a look at the
topics of "Normalization" and "relational database design".

In the situation you described, you could create a table with:

tblRevenue
ItemID
RevenueDate
RevenueAmt

You probably DON'T want to use the month (e.g., April, May, ...) because
those happen every year. Your design requires changing EVERYTHING (tables,
form, queries, reports, code, ...) each year to handle the new year's data.
The table structure I offered gives you a way to enter hundreds of years'
data without ever having to change your application.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,

The tables will be overwritten with data every month. And I need the item's
value per month. Once the report is generated out of the final calculations,
the table's data will have be changed on monthly basis. It is a forecast
table which is compared to a budget table.
So I am handling the database on yearly basis only.
I need to arrange the sum's of each month in a new table to be able to draw
charts in crystal reports

thanks
Joyce

 

[Jeff Boyce]

Joyce

What you describe sounds very much like ... a spreadsheet. Is there a
reason you aren't using Excel instead of Access?

If you want to be able to use the features/functions that Access offers, you
need to feed it relational data, not spreadsheet data.

Regards

Jeff Boyce
Microsoft Office/Access MVP