Update Query with 12 Criterias

[JavierDiaz4]

Hello there everyone,

I thought this was going to be easy, but it hasnt been. I have an update
query that I didnt want to maintain, so I created a table that looks like the
below

Item / Date / Quantity

008 / 0808 / 3000
008 / 0908 / 50000
008 / 1008 / 6500

Now the update query was suppose to take the quantities you see above and
update them to a flat table that I have by month. Which has the items
already in it but the months are empty, so the update query was going to
place the quantities in it. Like below

Item / Aug / Sept / Oct
008 / 3000 / 50000 / 6500

so my update query was suppose to have a join from the open orders query to
the item table by month and update based on a criteria like the below

Update August if Date=0808 then Quantity
Update Sept if Date = 0908 then Quantity

But the above updateto formula in its correct syntax is causing spotty
updates. Do I have to do an update / criteria for each month in its own
query?

Please help?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It's usually considered bad form/design to create a summary table like
your "Item / Aug / Sept / Oct" table. It is better to create a cross
tab query (Pivot Table query). Like this:

PARAMETERS start_date DateTime, end_date DateTime;
TRANSFORM SUM(quantity) As theValue
SELECT [item]
FROM table_name
WHERE item_date BETWEEN start_date And end_date
PIVOT Format(item_date, "yymm/mmm")

To get the date columns to be in chronological order we have to put in
the year and month (yymm) with the month name after (mmm).

The output of the above query will look like your output example.

Item Aug Sept Oct
008 3000 50000 6500

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMGpGYechKqOuFEgEQKyOQCcCoC+L3S5bDNF0pay4GsFERSsqmwAn1Lj
Q5jVd+sSRl60PVd5PMIje7mK
=HSa9
-----END PGP SIGNATURE-----

 

[JavierDiaz4]

Thanks MGFoster. I decided to go with what you suggested. Thanks again.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It's usually considered bad form/design to create a summary table like
your "Item / Aug / Sept / Oct" table. It is better to create a cross
tab query (Pivot Table query). Like this:

PARAMETERS start_date DateTime, end_date DateTime;
TRANSFORM SUM(quantity) As theValue
SELECT [item]
FROM table_name
WHERE item_date BETWEEN start_date And end_date
PIVOT Format(item_date, "yymm/mmm")

To get the date columns to be in chronological order we have to put in
the year and month (yymm) with the month name after (mmm).

The output of the above query will look like your output example.

Item Aug Sept Oct
008 3000 50000 6500

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMGpGYechKqOuFEgEQKyOQCcCoC+L3S5bDNF0pay4GsFERSsqmwAn1Lj
Q5jVd+sSRl60PVd5PMIje7mK
=HSa9
-----END PGP SIGNATURE-----

Hello there everyone,

I thought this was going to be easy, but it hasnt been. I have an update
query that I didnt want to maintain, so I created a table that looks like the
below

Item / Date / Quantity

008 / 0808 / 3000
008 / 0908 / 50000
008 / 1008 / 6500

Now the update query was suppose to take the quantities you see above and
update them to a flat table that I have by month. Which has the items
already in it but the months are empty, so the update query was going to
place the quantities in it. Like below

Item / Aug / Sept / Oct
008 / 3000 / 50000 / 6500

so my update query was suppose to have a join from the open orders query to
the item table by month and update based on a criteria like the below

Update August if Date=0808 then Quantity
Update Sept if Date = 0908 then Quantity

But the above updateto formula in its correct syntax is causing spotty
updates. Do I have to do an update / criteria for each month in its own
query?

Please help?