Urgent help - SQL query and table/query structures - WILL PAY

You'll insert one record for each area chosen. For help in that regard,
visit www.drdev.net and look at their tutorials regarding handling multiple
records in a single form.


"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:ebaciu$ep9$1@forums.macromedia.com...
> Thanks Jules,
>
> So, in effect, I need another table (6 in total), to achieve this. As I
> mentioned to Lionstone, I wanted to allow Van Drivers to select which
> operating areas they wish to view quotes for. My intention being to use
> an INSERT form for this (which is why I had the areas within the
> VanDriversAreas table). This would be my INSERT form:
>
> All UK <checkbox>
> All Scotland <checkbox>
> All Wales <checkbox>
> ...etc (16 checkboxes).
>
> If I create a seperate table for VanDriver areas, how do I insert this
> data into one table? It's almost like a multiple insert; is that
> possible?
>
> Thanks again for your input - it is a huge relief.
> Regard
> Nath.
>
> "Julian Roberts" <nospam@charon.co.uk> wrote in message
> news:ebab9v$d52$1@forums.macromedia.com...
>> OK, but the principle is the same. The key issue, as lionstone points
>> out, is that you should not have the areas listed as columns in the
>> VanDriversAreas table.
>>
>> Orders:
>> OrderID
>> RegionID
>>
>> Regions:
>> RegionID
>> Region
>>
>> Areas:
>> AreaID
>> Area
>>
>> AreaRegions:
>> AreaID
>> RegionID
>>
>> VanDrivers:
>> VanDriverID
>> VanDriver
>>
>> VanDriverAreas:
>> VanDriverID
>> AreaID
>>
>> and SQL
>>
>> select * from Orders where RegionID in (
>> select RegionID from AreaRegions where AreaID in (
>> select AreaID from VanDriverAreas where VanDriverID=5
>> )
>> )
>>
>>
>> --
>> Jules
>> http://www.charon.co.uk/charoncart
>> Charon Cart 3
>> Shopping Cart Extension for Dreamweaver MX/MX 2004
>>
>>
>>
>>
>>
>
>

Thanks Jules,

So, in effect, I need another table (6 in total), to achieve this. As I
mentioned to Lionstone, I wanted to allow Van Drivers to select which
operating areas they wish to view quotes for. My intention being to use an
INSERT form for this (which is why I had the areas within the
VanDriversAreas table). This would be my INSERT form:

All UK <checkbox>
All Scotland <checkbox>
All Wales <checkbox>
...etc (16 checkboxes).

If I create a seperate table for VanDriver areas, how do I insert this data
into one table? It's almost like a multiple insert; is that possible?

Thanks again for your input - it is a huge relief.
Regard
Nath.

"Julian Roberts" <nospam@charon.co.uk> wrote in message
news:ebab9v$d52$1@forums.macromedia.com...
> OK, but the principle is the same. The key issue, as lionstone points out,
> is that you should not have the areas listed as columns in the
> VanDriversAreas table.
>
> Orders:
> OrderID
> RegionID
>
> Regions:
> RegionID
> Region
>
> Areas:
> AreaID
> Area
>
> AreaRegions:
> AreaID
> RegionID
>
> VanDrivers:
> VanDriverID
> VanDriver
>
> VanDriverAreas:
> VanDriverID
> AreaID
>
> and SQL
>
> select * from Orders where RegionID in (
> select RegionID from AreaRegions where AreaID in (
> select AreaID from VanDriverAreas where VanDriverID=5
> )
> )
>
>
> --
> Jules
> http://www.charon.co.uk/charoncart
> Charon Cart 3
> Shopping Cart Extension for Dreamweaver MX/MX 2004
>
>
>
>
>

"You're not making any sense." :o)
I totally agree! That's why I need help because my brain goes so far with
this and then just implodes! For newbies like myself this type of thing is
very complicated. It is only through the kind help you are offering that I
will understand, and for that I am very grateful.

I was just thinking that, in order for the van drivers to mark which
operating areas they want to view quotes for, they would have to, somewhere,
mark this in the database, I had assumed by using an INSERT form. On my
INSERT page I wanted something simple like this:

Choose Operating Areas:

All UK <checkbox>
All Scotland <checkbox>
All Wales <checkbox>
...etc (16 checkboxes).

If I create a seperate table for VanDriver areas, as Jules is suggesting
(that has vandriverID and areaID), then how do I insert this data into one
table, one record for each checkbox? It's almost like a multiple INSERT?
Is that possible?

Hope to hear from you,
Regards
Nath.

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:eba8t8$a0u$1@forums.macromedia.com...
> You're not making any sense.
> Why do you have a table of operating areas, but then list out operating
> areas across the van drivers operating areas table as columns? And then
> your columns are foreign keys to boot. So does each driver have his own
> definition of what "all England" means? That's what your table structure
> is implying.
>
>
> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
> news:eb9nu1$il9$1@forums.macromedia.com...
>> Hi Jules.
>> Thank you for contributing to this thread. I think I need to explain
>> things more clearly. It is trying to combine these 5 tables that is
>> causing me a week long headache! Here are my tables:
>>
>> Orders:
>> OrderID (PK)
>> PickUpRegion (numeric - a foreign key to the RegionID PK in the Regions
>> table)
>> DeliveryRegion (numeric - a foreign key to the RegionID PK in the Regions
>> table)
>>
>> Regions:
>> RegionID (PK)
>> Region
>>
>> Operating Areas:
>> OpAreaID (PK)
>> OperatingArea
>>
>> Operating Area Regions:
>> OpAreaRegionsID (PK)
>> OpAreaID (FK)
>> RegionID (FK)
>>
>> Van Drivers Operating Areas (this table allows Van Drivers to select
>> which operating areas they want to view orders from)
>> VanDriverOpAreasID
>> VanDriverID (FK)
>> ALLUK (numeric - a foreign key to the OpAreaID PK in the Operating Areas
>> table)
>> ALLSCOTLAND (numeric - a foreign key to the OpAreaID PK in the Operating
>> Areas table)
>> ALLWALES (numeric - a foreign key to the OpAreaID PK in the Operating
>> Areas table)
>> NORTHSCOTLAND (numeric - a foreign key to the OpAreaID PK in the
>> Operating Areas table)
>> SOUTHSCOTLAND (numeric - a foreign key to the OpAreaID PK in the
>> Operating Areas table)
>> ...etc (there are 16 operating areas)
>>
>> In my Operating Area Regions link table, I have associated the regions to
>> the operating areas. I just can't figure out the SELECT statement I need
>> to use in order for Van Drivers to ONLY see orders from their selected
>> Operating Areas.
>>
>> Really appreciate this and, as indicated in my initial thread, I am happy
>> to pay for advice offered to help me solve this problem. Thank you.
>>
>> Regards
>> Nath.
>>
>> "Julian Roberts" <nospam@charon.co.uk> wrote in message
>> news:eb7uqb$c6o$1@forums.macromedia.com...
>>> It's not quite clear what your structure is. Is allengland a field or a
>>> record in the tblVanDriverOperatingAreas table. If it's a field, then
>>> your structure is wrong. I'd suggest:
>>>
>>> Orders:
>>> OrderID
>>> RegionID
>>>
>>> Regions:
>>> RegionID
>>> Region
>>>
>>> VanDrivers:
>>> VanDriverID
>>> VanDriver
>>>
>>> VanDriverRegions:
>>> VanDriverID
>>> RegionID
>>>
>>> Then to show orders for a van driver:
>>>
>>> select * from Orders
>>> where RegionID in (
>>> select RegionID from VanDriverRegions where VanDriverID=10
>>> )
>>>
>>> --
>>> Jules
>>> http://www.charon.co.uk/charoncart
>>> Charon Cart 3
>>> Shopping Cart Extension for Dreamweaver MX/MX 2004
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>

OK, but the principle is the same. The key issue, as lionstone points out,
is that you should not have the areas listed as columns in the
VanDriversAreas table.

Orders:
OrderID
RegionID

Regions:
RegionID
Region

Areas:
AreaID
Area

AreaRegions:
AreaID
RegionID

VanDrivers:
VanDriverID
VanDriver

VanDriverAreas:
VanDriverID
AreaID

and SQL

select * from Orders where RegionID in (
select RegionID from AreaRegions where AreaID in (
select AreaID from VanDriverAreas where VanDriverID=5
)
)


--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

You're not making any sense.
Why do you have a table of operating areas, but then list out operating
areas across the van drivers operating areas table as columns? And then
your columns are foreign keys to boot. So does each driver have his own
definition of what "all England" means? That's what your table structure is
implying.


"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:eb9nu1$il9$1@forums.macromedia.com...
> Hi Jules.
> Thank you for contributing to this thread. I think I need to explain
> things more clearly. It is trying to combine these 5 tables that is
> causing me a week long headache! Here are my tables:
>
> Orders:
> OrderID (PK)
> PickUpRegion (numeric - a foreign key to the RegionID PK in the Regions
> table)
> DeliveryRegion (numeric - a foreign key to the RegionID PK in the Regions
> table)
>
> Regions:
> RegionID (PK)
> Region
>
> Operating Areas:
> OpAreaID (PK)
> OperatingArea
>
> Operating Area Regions:
> OpAreaRegionsID (PK)
> OpAreaID (FK)
> RegionID (FK)
>
> Van Drivers Operating Areas (this table allows Van Drivers to select which
> operating areas they want to view orders from)
> VanDriverOpAreasID
> VanDriverID (FK)
> ALLUK (numeric - a foreign key to the OpAreaID PK in the Operating Areas
> table)
> ALLSCOTLAND (numeric - a foreign key to the OpAreaID PK in the Operating
> Areas table)
> ALLWALES (numeric - a foreign key to the OpAreaID PK in the Operating
> Areas table)
> NORTHSCOTLAND (numeric - a foreign key to the OpAreaID PK in the
> Operating Areas table)
> SOUTHSCOTLAND (numeric - a foreign key to the OpAreaID PK in the
> Operating Areas table)
> ...etc (there are 16 operating areas)
>
> In my Operating Area Regions link table, I have associated the regions to
> the operating areas. I just can't figure out the SELECT statement I need
> to use in order for Van Drivers to ONLY see orders from their selected
> Operating Areas.
>
> Really appreciate this and, as indicated in my initial thread, I am happy
> to pay for advice offered to help me solve this problem. Thank you.
>
> Regards
> Nath.
>
> "Julian Roberts" <nospam@charon.co.uk> wrote in message
> news:eb7uqb$c6o$1@forums.macromedia.com...
>> It's not quite clear what your structure is. Is allengland a field or a
>> record in the tblVanDriverOperatingAreas table. If it's a field, then
>> your structure is wrong. I'd suggest:
>>
>> Orders:
>> OrderID
>> RegionID
>>
>> Regions:
>> RegionID
>> Region
>>
>> VanDrivers:
>> VanDriverID
>> VanDriver
>>
>> VanDriverRegions:
>> VanDriverID
>> RegionID
>>
>> Then to show orders for a van driver:
>>
>> select * from Orders
>> where RegionID in (
>> select RegionID from VanDriverRegions where VanDriverID=10
>> )
>>
>> --
>> Jules
>> http://www.charon.co.uk/charoncart
>> Charon Cart 3
>> Shopping Cart Extension for Dreamweaver MX/MX 2004
>>
>>
>>
>>
>>
>
>

Hi Jules.
Thank you for contributing to this thread. I think I need to explain things
more clearly. It is trying to combine these 5 tables that is causing me a
week long headache! Here are my tables:

Orders:
OrderID (PK)
PickUpRegion (numeric - a foreign key to the RegionID PK in the Regions
table)
DeliveryRegion (numeric - a foreign key to the RegionID PK in the Regions
table)

Regions:
RegionID (PK)
Region

Operating Areas:
OpAreaID (PK)
OperatingArea

Operating Area Regions:
OpAreaRegionsID (PK)
OpAreaID (FK)
RegionID (FK)

Van Drivers Operating Areas (this table allows Van Drivers to select which
operating areas they want to view orders from)
VanDriverOpAreasID
VanDriverID (FK)
ALLUK (numeric - a foreign key to the OpAreaID PK in the Operating Areas
table)
ALLSCOTLAND (numeric - a foreign key to the OpAreaID PK in the Operating
Areas table)
ALLWALES (numeric - a foreign key to the OpAreaID PK in the Operating Areas
table)
NORTHSCOTLAND (numeric - a foreign key to the OpAreaID PK in the Operating
Areas table)
SOUTHSCOTLAND (numeric - a foreign key to the OpAreaID PK in the Operating
Areas table)
...etc (there are 16 operating areas)

In my Operating Area Regions link table, I have associated the regions to
the operating areas. I just can't figure out the SELECT statement I need to
use in order for Van Drivers to ONLY see orders from their selected
Operating Areas.

Really appreciate this and, as indicated in my initial thread, I am happy to
pay for advice offered to help me solve this problem. Thank you.

Regards
Nath.

"Julian Roberts" <nospam@charon.co.uk> wrote in message
news:eb7uqb$c6o$1@forums.macromedia.com...
> It's not quite clear what your structure is. Is allengland a field or a
> record in the tblVanDriverOperatingAreas table. If it's a field, then your
> structure is wrong. I'd suggest:
>
> Orders:
> OrderID
> RegionID
>
> Regions:
> RegionID
> Region
>
> VanDrivers:
> VanDriverID
> VanDriver
>
> VanDriverRegions:
> VanDriverID
> RegionID
>
> Then to show orders for a van driver:
>
> select * from Orders
> where RegionID in (
> select RegionID from VanDriverRegions where VanDriverID=10
> )
>
> --
> Jules
> http://www.charon.co.uk/charoncart
> Charon Cart 3
> Shopping Cart Extension for Dreamweaver MX/MX 2004
>
>
>
>
>

It's not quite clear what your structure is. Is allengland a field or a
record in the tblVanDriverOperatingAreas table. If it's a field, then your
structure is wrong. I'd suggest:

Orders:
OrderID
RegionID

Regions:
RegionID
Region

VanDrivers:
VanDriverID
VanDriver

VanDriverRegions:
VanDriverID
RegionID

Then to show orders for a van driver:

select * from Orders
where RegionID in (
select RegionID from VanDriverRegions where VanDriverID=10
)

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

Hi again.

I feel that I have my tables structured incorrectly, in particular this one,
and I'd appreciate your thoughts/suggestions:

tblVanDriverOperatingAreas
operatingareaID (1 - PK)
vandriverID (1 - FK)
allengland (1 - int)
allscotland (2 - int)
allwales (3 - int)
withinM25 (7 - int)
northeastengland (8 - int)

I need to allow van drivers to select which operating areas they want to
view jobs from, but in the above table all of the operating areas would have
the same ID, wouldn't they?

Should I create ANOTHER table, simply listing the operating areas, as well
as the van driver operating area selection table?

tblOperatingAreas
opareaID (1 - PK)
opareaname (withinM25 - nvarchar)

tblVanDriverOperatingAreas
operatingareaID (1 - PK)
vandriverID (1 - FK)
allengland
allscotland
allwales
withinM25 (1 - would this be a Foreign Key?)
northeastengland

Then, when I allow the van driver to add/update their details (via an
INSERT/UPDATE form in a CRM area of the web site), I would simply insert the
appropriate opareaID into each of the fields in tblVanDriverOperatingAreas?

Sorry to sound so confused but I've been staring at this for over a week now
and I've tried that many combinations of solutions that I've totally lost
where I'm at!

Really appreciate your help. If it makes it easier, I'm happy to send you
the URL to the CRM area so you can see what I am trying to achieve?

Regards
Nath.

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:eb7p0j$570$1@forums.macromedia.com...
> You need to assign each region to an operating area. That will allow you
> to link your operating areas to regions, and then regions to orders.
>
>
> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
> news:eb7nle$3jk$1@forums.macromedia.com...
>> Going round in circles with this and have spent almost a week trying to
>> solve it, with no joy so far. I'm happy to pay someone to help me make
>> sense of my tables, queries and relationships.
>>
>> Tables are as follows (sample data, and field type, in brackets):
>>
>> tblOrders
>> orderID (1 - PK)
>> pickupregion (10 - int - FK)
>> deliveryregion (10 - int - FK)
>>
>> tblRegions
>> regionID (10 - PK)
>> regionname (London - text)
>>
>> tblVanDrivers
>> vandriverID (1 - PK)
>> name (Joe Bloggs - text)
>> address (High Street - text)
>>
>> tblVanDriverOperatingAreas
>> operatingareaID (1 - PK)
>> vandriverID (1 - FK)
>> allengland (1 - int)
>> allscotland (2 - int)
>> allwales (3 - int)
>> withinM25 (7 - int)
>> northeatengland (8 - int)
>>
>> I need to display a list of orders that match the operating areas
>> selected by any one van driver. For example, if a van driver selects
>> "withinM25" as their preferred operating area, then I'd only want them to
>> be able to view jobs in that region. Similarly, if the van operator
>> chooses "All England" then I'd also need to display the jobs "withinM25"
>> as this would also qualify.
>>
>> Have I gone about creating my tables in the wrong way?
>>
>> If I allowed the van operators to select regions, rather than operating
>> areas, then my problem would be solved as I understand how to do that,
>> however it's the operating areas that are giving me grief because I don't
>> know how to relate them to anything else.
>>
>> Also, I would like to keep the tblVanDrivers and
>> tblVanDriverOperatingAreas in seperate tables.
>>
>> Like I say, I'm happy to pay someone for an hours time in order to solve
>> this riddle that it doing my head in!
>> Thanking you in advance.
>>
>> Regards
>> Nath.
>>
>>
>>
>>
>>
>>
>>
>>
>> I want to be able to display a list of orders that match a van operators
>> chosen operating areas.
>>
>>
>>
>
>

Hi Lionstone. Thank you so much for responding - I was beginning to think
that I was being purposefully ignored as I've had no response to my last few
posts!

So, I would create a new table, a link table as I understand it, that
related regions to operating areas?

linkrowID
operatingareaID
regionID

Will just go and try that....but will no doubt be back with a query or two!
Thanks again.

Nath.

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:eb7p0j$570$1@forums.macromedia.com...
> You need to assign each region to an operating area. That will allow you
> to link your operating areas to regions, and then regions to orders.
>
>
> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
> news:eb7nle$3jk$1@forums.macromedia.com...
>> Going round in circles with this and have spent almost a week trying to
>> solve it, with no joy so far. I'm happy to pay someone to help me make
>> sense of my tables, queries and relationships.
>>
>> Tables are as follows (sample data, and field type, in brackets):
>>
>> tblOrders
>> orderID (1 - PK)
>> pickupregion (10 - int - FK)
>> deliveryregion (10 - int - FK)
>>
>> tblRegions
>> regionID (10 - PK)
>> regionname (London - text)
>>
>> tblVanDrivers
>> vandriverID (1 - PK)
>> name (Joe Bloggs - text)
>> address (High Street - text)
>>
>> tblVanDriverOperatingAreas
>> operatingareaID (1 - PK)
>> vandriverID (1 - FK)
>> allengland (1 - int)
>> allscotland (2 - int)
>> allwales (3 - int)
>> withinM25 (7 - int)
>> northeatengland (8 - int)
>>
>> I need to display a list of orders that match the operating areas
>> selected by any one van driver. For example, if a van driver selects
>> "withinM25" as their preferred operating area, then I'd only want them to
>> be able to view jobs in that region. Similarly, if the van operator
>> chooses "All England" then I'd also need to display the jobs "withinM25"
>> as this would also qualify.
>>
>> Have I gone about creating my tables in the wrong way?
>>
>> If I allowed the van operators to select regions, rather than operating
>> areas, then my problem would be solved as I understand how to do that,
>> however it's the operating areas that are giving me grief because I don't
>> know how to relate them to anything else.
>>
>> Also, I would like to keep the tblVanDrivers and
>> tblVanDriverOperatingAreas in seperate tables.
>>
>> Like I say, I'm happy to pay someone for an hours time in order to solve
>> this riddle that it doing my head in!
>> Thanking you in advance.
>>
>> Regards
>> Nath.
>>
>>
>>
>>
>>
>>
>>
>>
>> I want to be able to display a list of orders that match a van operators
>> chosen operating areas.
>>
>>
>>
>
>

You need to assign each region to an operating area. That will allow you to
link your operating areas to regions, and then regions to orders.


"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:eb7nle$3jk$1@forums.macromedia.com...
> Going round in circles with this and have spent almost a week trying to
> solve it, with no joy so far. I'm happy to pay someone to help me make
> sense of my tables, queries and relationships.
>
> Tables are as follows (sample data, and field type, in brackets):
>
> tblOrders
> orderID (1 - PK)
> pickupregion (10 - int - FK)
> deliveryregion (10 - int - FK)
>
> tblRegions
> regionID (10 - PK)
> regionname (London - text)
>
> tblVanDrivers
> vandriverID (1 - PK)
> name (Joe Bloggs - text)
> address (High Street - text)
>
> tblVanDriverOperatingAreas
> operatingareaID (1 - PK)
> vandriverID (1 - FK)
> allengland (1 - int)
> allscotland (2 - int)
> allwales (3 - int)
> withinM25 (7 - int)
> northeatengland (8 - int)
>
> I need to display a list of orders that match the operating areas selected
> by any one van driver. For example, if a van driver selects "withinM25"
> as their preferred operating area, then I'd only want them to be able to
> view jobs in that region. Similarly, if the van operator chooses "All
> England" then I'd also need to display the jobs "withinM25" as this would
> also qualify.
>
> Have I gone about creating my tables in the wrong way?
>
> If I allowed the van operators to select regions, rather than operating
> areas, then my problem would be solved as I understand how to do that,
> however it's the operating areas that are giving me grief because I don't
> know how to relate them to anything else.
>
> Also, I would like to keep the tblVanDrivers and
> tblVanDriverOperatingAreas in seperate tables.
>
> Like I say, I'm happy to pay someone for an hours time in order to solve
> this riddle that it doing my head in!
> Thanking you in advance.
>
> Regards
> Nath.
>
>
>
>
>
>
>
>
> I want to be able to display a list of orders that match a van operators
> chosen operating areas.
>
>
>