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 wrote:
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=Count, S
Retailer, TR=Count,NS,C=>6
then run.

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

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

The Frog wrote:
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?

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 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

The Frog <Mr.Frog.to.you (AT) googlemail (DOT) com> wrote:

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/

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

The Frog <Mr.Frog.to.you (AT) googlemail (DOT) com> wrote:

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>

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

<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/

Hi Tony,

The issue seem to stem from the particular style of install this firm
has opted for in its XP rollout (the 2K image is horrific to say the
least). What I think they have done is used parts of installs from
different language versions to try and 'match' them to the country in
which they are used. What you end up with is a mish mash of
functionality that doesnt really hold together. The IT group is always
'fixing' issues they themselves have created, and in the process
usually breaking more and more things. Achieving a stable build would
be great.

I learned very quickly in this organisation to remove any dependancies
that I could, as you never know if the dll will even exist on the
machine the next day. My instincts tell me that these people are
trying to make early 90's infrastructure work with more modern
technology, and not doing a very good job of it. More scripts running
during startup than I can actually count. This group has taken the ROI
mentality way too far and are justifying it in the grounds that the
software / OS doesnt cost them anything. Utter rubbish - the lost
productivity alone can be measured in the tens of thousands of hours
globally per year. Idiots revelling in their mindlessness. Anyway,
thats enough of me bitching on :-)

Terrifying as it is, it is what must be dealt with. And Java keeps
looking better each day - even if it is an old version. Write the app
in Java, set up a DSN or just use ODBC, and leave the data in Jet.

Cheers and thanks for trying to help. I appreciate it. Without the
experience of you guys I would be beating my head against a wall very
often indeed.

The Frog