SQL Problem

[The Frog]

Hi everyone,

I am having some trouble with an SQL statement that I just cant seem
to get to work. I have imported some data into MS Access from another
application and have a single table with information in it.

There are a number of columns, and I am trying to do a 'summary' type
query for a quick view of the information. The problem comes in when I
am trying to place a condition on the select statement. I am trying to
work it as follows:

There are several products advertised each week, and this is
represented by rows with data in the following columns, CW for
Calender week, Retailer for the store that did the advertising, and
Product for the product that was advertised. I also have other columns
such as Price etc....

The result that I am trying to achieve is to have a single query that
lists one row per product that has been advertised in any given week
by at least 7 retailers, the number of times that this has occurred,
and the average price for these occurrences.

So in short it is supposed to look like:
Number_of_Weeks Average_Price
Product1 # EURO
Product2 # EURO
....and so on

I am having a complete mental block with this one, even with several
strong coffees! If anyone can point me in the right direction I would
greatly appreciate it.

Cheers

The Frog

[Salad]

Re: SQL Problem

The Frog wrote:

Hi everyone,
>
I am having some trouble with an SQL statement that I just cant seem
to get to work. I have imported some data into MS Access from another
application and have a single table with information in it.
>
There are a number of columns, and I am trying to do a 'summary' type
query for a quick view of the information. The problem comes in when I
am trying to place a condition on the select statement. I am trying to
work it as follows:
>
There are several products advertised each week, and this is
represented by rows with data in the following columns, CW for
Calender week, Retailer for the store that did the advertising, and
Product for the product that was advertised. I also have other columns
such as Price etc....
>
The result that I am trying to achieve is to have a single query that
lists one row per product that has been advertised in any given week
by at least 7 retailers, the number of times that this has occurred,
and the average price for these occurrences.
>
So in short it is supposed to look like:
Number_of_Weeks Average_Price
Product1 # EURO
Product2 # EURO
...and so on
>
I am having a complete mental block with this one, even with several
strong coffees! If anyone can point me in the right direction I would
greatly appreciate it.
>
Cheers
>
The Frog

This might get you close.
Open a new query, add new table. From menu, select View/Totals.
TR=TotalRow, C = Criteria. S = Show, NS = NoShow. Drag
Product, TR=GroupBy, S
AdDate, TR=Where, NS, C=>=[Enter Ad Start Date]
AdDate, TR=Where, NS, C=<=[Enter Ad End Date]
Price, TR=AVG, S
Product,TR=Coun t, S
Retailer, TR=Count,NS,C=> 6
then run.

Stolen Car

http://www.youtube.com/watch?v=qWoMq46g0XU

[The Frog]

Re: SQL Problem

Unfortunately I cannot do this as I have only got Access97 to work
with and the DB is in Jet4.0 format. I am trying to do this via SQL
using MS Query actually! In a nutshell it looks like I cant achieve
this because I cant get the more complex where criteria to function
(seemingly).

A single SQL statement would be my only way it seems. Unfortunately I
cannot use the functionality in Access to achieve this result or I
would be well done by now!

Cheers and thanks for trying anyway

The Frog

[Salad]

Re: SQL Problem

The Frog wrote:

Unfortunately I cannot do this as I have only got Access97 to work
with and the DB is in Jet4.0 format. I am trying to do this via SQL
using MS Query actually! In a nutshell it looks like I cant achieve
this because I cant get the more complex where criteria to function
(seemingly).

I guess, actually know, I am confused. You stated "I have imported some
data into MS Access from another application and have a single table
with information in it." You state also "the DB is in Jet4.0 format".
I don't know what that means...is it in A2000+ format? Are you saying
A97 can't read the table?

I know you've been around the block a few times and the query you
described didn't seem to be one of difficulty for your skills. My
solution wouldn't work if your version of Access can't read the table.

Is it possible the app that supplied the data can export the data you
require into a text file and then link to the text file?

A single SQL statement would be my only way it seems. Unfortunately I
cannot use the functionality in Access to achieve this result or I
would be well done by now!
>
Cheers and thanks for trying anyway
>
The Frog
>

[DFS]

Re: SQL Problem

SELECT WeekBegin, WeekEnding, ProductID, Count(ProductID ) as Advertised,
Avg(Price) as AvgPrice
FROM Table
GROUP BY WeekBegin, WeekEnding, ProductID
HAVING Count(ProductID ) >= 7;




The Frog wrote:

Hi everyone,
>
I am having some trouble with an SQL statement that I just cant seem
to get to work. I have imported some data into MS Access from another
application and have a single table with information in it.
>
There are a number of columns, and I am trying to do a 'summary' type
query for a quick view of the information. The problem comes in when I
am trying to place a condition on the select statement. I am trying to
work it as follows:
>
There are several products advertised each week, and this is
represented by rows with data in the following columns, CW for
Calender week, Retailer for the store that did the advertising, and
Product for the product that was advertised. I also have other columns
such as Price etc....
>
The result that I am trying to achieve is to have a single query that
lists one row per product that has been advertised in any given week
by at least 7 retailers, the number of times that this has occurred,
and the average price for these occurrences.
>
So in short it is supposed to look like:
Number_of_Weeks Average_Price
Product1 # EURO
Product2 # EURO
...and so on
>
I am having a complete mental block with this one, even with several
strong coffees! If anyone can point me in the right direction I would
greatly appreciate it.
>
Cheers
>
The Frog

[The Frog]

Re: SQL Problem

Hi All,

The problem is that the Jet4.0 (Access 2000) type file cant be read by
Access97. Unfortunately that is what I am saddled with for this
particular task. So I must find another way to get at the data. I was
trying to use MS Query as an alternative, but the type of query I
would normally use, such as the one shown by DFS (Thankyou by the
way), doesnt seem to want to work and I have no idea why.

In the end what I did was to write a small app that created an ADO
connection to the db, ran the query results into a recordset, and
exported the recordset as XML.

I then made a blank Access97 mdb and used Apatar to 'pump' the xml
file into a table (I made the table). All the rest I could do from
there.

Pain in the butt, however it seems that the problem is solved - albeit
not so elegantly. If this issue comes up again I will just bring my
private laptop to work, copy the data to a usb key, and take care of
it there!

Thanks all for your help and patience. I appreciate the time you have
spent on this, and am grateful for your efforts.

Kind Regards

The Frog

[Tony Toews [MVP]]

Re: SQL Problem

The Frog <Mr.Frog.to.you @googlemail.com wrote:

>The problem is that the Jet4.0 (Access 2000) type file cant be read by
>Access97.

Try using MS DAO 3.6 in A97 instead of DAO 3.5. There was a posting a while back and
a very recent posting indicating this worked fine for the person.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[The Frog]

Re: SQL Problem

Hi Tony,

Yeah, that was me. The dao3.6 works only if it is correctly installed.
The corporate environment I am in hasnt done that and I am unable to
do anything about it (so it seems). I have had partial success with
this approach but it is by no means guaranteed from my trials.

I can get it to work on a pc at home, but on the work ones forget it.
It looks like the issue is getting everything installed / registered
the right way, and in my current corporate environment there are no
guarantees as the IT "Guru's" change the environment each day and
never tell anyone what they are doing. DLL's will magically
diapappear, some will simply be changed without updating things in the
registry, and on and on.... Suffice to say we have a lot of problems
that make our systems completely unreliable. I have even had instances
where Excel cant add correctly on one machine on one day, and its
neighbour the next! I swear I am seriously thinking of just going Java
for everything at the moment - a bit more development time for simple
things but I will get it back in the support time for sure.

Anyway, problem solved for now. In the future I will just use Apatar
or KETTLE to push data around and be done with it. Maybe I should
shift everything to Postgres....

Thanks for your help guys

Cheers

The Frog

[Tony Toews [MVP]]

Re: SQL Problem

The Frog <Mr.Frog.to.you @googlemail.com wrote:

>Yeah, that was me.

My apologies. I don't keep track of names all the well in the forums.

Of course my reply would've been better if I'd added the word credible in front of
posting. <smile>

>The dao3.6 works only if it is correctly installed.

However the core Win XP OS requires Jet 4.0 So I would've thought that would be
jsut fine.

>It looks like the issue is getting everything installed / registered
>the right way, and in my current corporate environment there are no
>guarantees as the IT "Guru's" change the environment each day and
>never tell anyone what they are doing. DLL's will magically
>diapappear, some will simply be changed without updating things in the
>registry, and on and on.... Suffice to say we have a lot of problems
>that make our systems completely unreliable.

<sigh Best wishes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/