MS ACCESS

[memalef]

i have one table with various information that is updated by me every time.

i would like to do by query another table that will include four fields:

"COMPANY", "TOTAL", "LAST_MONTH", "LAST_WEEK"

and collect information for all these field by different criteria.

Main table looks like this:

| ID | SHIPPING_DATE | COMPANY_NAME | CHARGE | GLASS | OTHER |

ID - AUTO NUMBER
SHIPPING_DATE - DATE/TIME
CHARGE - CURRENCY
GLASS - CURRENCY
OTHER - CURRENCY

Second table has to be :

| COMPANY | TOTAL | LAST_MONTH | LAST_WEEK |

when field "TOTAL" equal to ("CHARGE" + "GLASS" + "OTHER") GROUP BY
COMPANY_NAME
second field "LAST_MONTH" does same expression and shows only result of last
month
and third shows only result of week.

How can i insert different information into fields of second table ?

 

[John W. Vinson]

i have one table with various information that is updated by me every time.

i would like to do by query another table that will include four fields:

"COMPANY", "TOTAL", "LAST_MONTH", "LAST_WEEK"

and collect information for all these field by different criteria.

Main table looks like this:

| ID | SHIPPING_DATE | COMPANY_NAME | CHARGE | GLASS | OTHER |

ID - AUTO NUMBER
SHIPPING_DATE - DATE/TIME
CHARGE - CURRENCY
GLASS - CURRENCY
OTHER - CURRENCY

Second table has to be :

| COMPANY | TOTAL | LAST_MONTH | LAST_WEEK |

when field "TOTAL" equal to ("CHARGE" + "GLASS" + "OTHER") GROUP BY
COMPANY_NAME
second field "LAST_MONTH" does same expression and shows only result of last
month
and third shows only result of week.

How can i insert different information into fields of second table ?

Since the information in the "second table" can be derived from the data in
the first table, the second table should simply *not exist*. If you have data
for "last week" in the table, one week from now that data WILL BE WRONG; if
you have a field for TOTAL and change one record in your main table the total
will be wrong, with no way to detect it!

Instead, use Totals queries to dynamically *calculate* last week's or last
month's data. The query for last week would be (in SQL view)

SELECT COMPANY_NAME, Sum([Charge]) + Sum([Glass]) + Sum([Other]) AS LAST_WEEK
WHERE SHIPPING_DATE BETWEEN DateAdd("ww", -1, Date()) AND Date();

You'ld use a criterion of BETWEEN DateAdd("m", -1, Date()) AND Date() to get
last month, and no criterion at all to get a grand total for the company.

You can display the results of the three queries on one report using
subreports, or a UNION query.