Re: Can I get Consultancy advice on SQL/Fox in the UK (South East)

Thanks Cindy, I am working through that but.....

My problem is the two Sequel Server tables within one database with which I
need to work are 'nastily' linked. I have managed to create views of each as
requierd. The principle is that it is a pair of files, (orderheader and
orderdetail). The key field is the orderheader.order field. It is a character
field which increments numerically (1000001, 1000002, 100003) with each new
order placed. Each order is made up of 'items' which can be ordered within a
single order (orderheader). Don't ask me why but the SQL designers have NOT
given an independent field for each part of an order but have concatenated a
'/' and the 'number' of the item from each order. So, header.order = 5678 and
it is assocaited with orderdetail.orderno 5678/1, 5678/2, 5678/3 and so on up
to an apparent max of 20 items on an order. I assume the need for the '/'
forced them to use character fields instead of numerical fields
(brilliant!!!).

Secondly, only the orderheader contains the date of the order and I need to
extract/collect a single file of combined orderheader and orderdetails for
'this week, last week and this week last year. I know that the
orderheader.orderdate is held in the Sequel files in 'smalldatetime'.

I would thus like to produce an SQL command that would let me specify a
single week to pull through those records into a single file. Once I can link
to two files and produce a foxpro 'table' form the sequel server request, I
can work out how to bring the rest of the data which I need. I simply do not
know is enough Sequel Server commands to combine that request into a single
command that I can initiate with SQLCONNECT, SQLPROP SQLPREPARE (etc.)
command.

Any assistance with that little lot would get me out of deep...............

Ian

"Cindy Winegarden" wrote:

> Hi Ian,
>
> Again, do NOT define your view to select all the records from the table. You
> can't possibly _read_ all the records so don't retrieve them. To filter the
> data so you get only a reasonable number of records, do the following:
>
> 1. Open the View Designer, choose a connection, and add a table (or
> combination of tables).
> 2. Fields tab - Use the Add> or Add All>> buttons to choose the fields
> (columns) you want to see.
> 3. Join tab - you won't need anything here unless you are combining records
> from more than one table in your view. I assume you are using only one
> table.
> 4. Filter tab - This is where you can limit the number of records. I've got
> a view to a Counties table. In the box under "Field Name" - I'm choosing
> "County_Name" which is a field in my Counties table. There's a button under
> "Not" which you can click if you want to choose records that are not equal
> to your criteria. In my example I'm going to leave this alone. In the
> "Criteria" column I'm going to use "=". In the Example column is where the
> filter actually happens so I'll talk about that next.
> 5. Example column -
> A) First let's assume I always want the same subset of rows from my
> view. I want Counties beginning with 'A'. I pull down the list under the
> Field Name and choose <Expression...> from the list. A box comes up where I
> type Left(Counties.County_Name, 1). Moving across I choose = for the
> Criteria and I put 'A' in the Example box. When I save the view and then
> open it I get counties beginning with A.
> B) Let's say I don't want the same criteria every time I open the view -
> I want to specify it each time. Instead of putting 'A' in the Example box I
> put "?MyCounty" (without the quotes) in the box. When I save the view and
> then open it I get an "input box" asking me to put in a value for MyCounty.
> I can put in 'A', 'B', or whatever I want. This gives me about 10 rows,
> which is a small enough amount that it is easy to work with.
> C) Let's say you don't want the input box coming up for the user to type
> in. You can specify a variable called MyCounty and give it a value. As long
> as the variable is in scope the view will find it. For example, I create a
> variable and give it a value by typing MyCounty = 'A' in the Command window.
> When I open the view, FoxPro notices that I've got a variable with the right
> name available and uses that for the view.
>
> Now, I can specify more than one criteria by adding rows to the list on the
> Filter tab. For example, rather than reading a whole telephone directory I
> could enter LastName = 'B' , City = 'Durham' , and Street = 'Elm'. This way
> I get only a few rows from the server.
>
> While you're in the View Designer, right-click and view the SQL code. You
> can create a view in code, without using the View Designer at all by typing
> code in a PRG file and then running it. You start out with "Create SQL View
> As ", followed by the SQL code. Check out the Create SQL View command in
> Help.
>
> Please post back with more questions after you've tried the steps above.
>
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@xxxxxxx www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "Barley Man" <BarleyMan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:CC68B9B2-599A-439F-B489-A4816C6B74D9@xxxxxxxxxxxxxxxx
> > Are there any Fox people in the South East UK (preferably around Cambridge
> > area) who would let me PAY them for advice on setting up and using best
> > practice for connecting FoxPro to SQL. It seems SO Evil and Alien as the
> > help
> > messages and reaction of the system seem truly dismal!
> >
> > I really need to visit someone with my PC to show them the problems.
> > Wthout
> > that, it's hard to discover what I am doing so badly!
> >
> > Ian
>
>
>
.