Not Shure if this is the right place.....

[TheEvilZX2]

I have been trying to teach myself SQL for the past few days. I work in an automotive shop and we use Winworks Motor Marketing machine, which uses SQL. I have succesfully searched for and found some of the thigns i was trying to figure out using SQL but im stumped on this one.

Basically I am trying to get a list of all of our customers who have spent money with us in the years 2005 and 2004.

the SQL statement i thought was going to work was:

select * from customer where dollars spent > 0 since #01/01/05# and #12/30/05

The marketing machine tells me:

Invalid SQL statement. Error: Syntax error (missing operator) in query expression 'dollarsspent > 0 since #01/01/05# and #12/30/05


We bought the SQL for Dummies Book and it doesnt seem to help much.

Can anyone tell me what im doing wrong or how to ask it exactly what i want?

 

[gmmastros]

You haven't given us enough information to help you properly.

Hopefully, you are running your queries in Query Analyzer. If so, run this query and post the results back here.

Select Column_Name, Data_Type
From   Information_Schema.Columns
Where  Table_Name = 'Customer'
[code]

With this information, we should be able to help you better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TheEvilZX2]

im new to all of this and i dont believe we have a query analyzer, what is it and how can i get it to give you better information?

 

[gmmastros]

In your original post, you indicated that you had some success. What application were you using so far?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TheEvilZX2]

were using motor marketing machine its an automotive shop program...we dont have an actual SQL program, just motor marketing machine which incorporates SQL to search for customers.

i have had success with querys like who hasnt been here for 5 to 9 months

select * from customer where lastin between #11/01/05# and #03/01/06


and that works to bring me the people who havent been here in 5 to 9 months

 

[gmmastros]

It appears as though the application is using a Microsoft Access database. Of course, this is just a hunch.

In SQL Server, dates are delimited with apostrophes. In Access, dates are delimited with the pound (#) sign.

Do a search on your computer for any file with a .mdb extension. If you find a database that appears to belong to this motorworks application with a .mdb extension, then you are definetely using Access. If this application does use an Access database, then I suggest you post questions in one of the Access forums here at Tek-Tips.

Without knowing the table structure, it will be impossible for us to give you any better advice. It seems reasonable for the customer table to have a lastin field. However, there is probably another table of data for each visit. You should look in to this if possible.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TheEvilZX2]

ok thanks, i found a dozen or so .mdb files in the motor program folder. when i open any one of them it shows no data, but its definately an access file.


mabye ill just take this over to the access forums

thanks again!

 

[TheEvilZX2]

ok i posted in the access forums about not being able to .open the files but i managed to open one and find the customer database.

now when you say table structure i am guessing that means the fields that go across the top?

if so then there isnt another field for each visit. only lastin and since (which is customer since the date). there is also a visits field but the value is not a date it is a number.

is there any other information i can give you to help me solve the problem of who spent money with us in the 2005, and 2004 years?

 

[gmmastros]

Generally speaking a database is made up of tables(s) and each table has fields (some prefer to call them columns).

There should be multiple tables within the database. Hopefully one of them will contain the 'visits' information.

I see it like this...

I don't know the difference between a muffler and a piston (and I shouldn't have to). So... i take my car to the garage for everything. At least 3 or 4 times a year I go in just to get the oil changed.

I should have 1 record in the customer table, with stuff like... my name, address, phone number, etc...

There should be another table the has 'visit' information. Each time I take my car to the garage, there should be a new record created in this table that stores 'visit' specific information (like date, which mechanic worked on my car, etc...).

Since each customer can have multiple visits, you need to have another table to store this info.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom