Linking Multiple Tables in an SQL Query

Hello,

I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the plants table: Common_Name, Latin_Name and Thumb_URL.

So far I've been able to use an INNER JOIN with the Size table to obtain the ProductID and Category ID using...

SELECT * FROM ShoppingCart INNER JOIN Size ON ShoppingCart.UniqueID = Size.UniqueID WHERE CartID = @.CartID ORDER BY ProductID

I now need to be able to use the ProductID and Category ID I've obtained from this part of the query to reference the Common Name, Latin Name etc of the data item from the plants table. Is there any way that I could do this?

I do sometimes wonder if I'm making this whole thing a lot more complicated than it needs to be. At the moment I'm storing details about the different types of plants the garden centre sells in the "plants" table. However, some plants are available in different sizes so I've got a table called "Size" which links to the plants table using the ProductID and CategoryID of data items. The Size table has a unique primary key field called "UniqueID" which is used to uniquely identify every plant that the garden centre sells (this cannot be done using Product/CategoryID becuase different sizes of the same plant have the same ProductID/CategoryID). I'm then storing just the CartID, UniqueID and Quantity in the "ShoppingCart" table. There must be an easier way of structuring the whole thing!

I'd really appreciate any help you can offer me.

Many thanks,

Luke

I don't think you should use just one or two tables to create a shopping cart, the commerce starter kit from Microsoft shopping cart is seven tables, you may want to just clone those and use because it includes all the stored proc you will need. Click on the commerce starter kit white paper for details. Hope this helps.

http://www.asp.net/downloads/default11.aspx?tabid=62

Linking a report to a dataset

I copy a report by viewing the code and pasting into a new report. The
problem is that the fields do not display for expressions etc. It gives the
following error "report item not linked to a dataset". How do I link the
report to a dataset so I can see the list of available dataset fields?
ThanksCopy and paste the report instead. Right mouse click on report, copy. Right
mouse click on project (not report folder) and paste. Then rename to
whatever you want. That gets the whole report including the dataset.
Not very discoverable. I would expect to be able to paste into the reports
folder.
Bruce L-C
"Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
news:%23p5b5pcmEHA.952@.TK2MSFTNGP14.phx.gbl...
> I copy a report by viewing the code and pasting into a new report. The
> problem is that the fields do not display for expressions etc. It gives
the
> following error "report item not linked to a dataset". How do I link the
> report to a dataset so I can see the list of available dataset fields?
> Thanks
>|||Is there anyway to link the report to a dataset if I did it the other way?
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OXYb8HdmEHA.1672@.TK2MSFTNGP14.phx.gbl...
> Copy and paste the report instead. Right mouse click on report, copy.
> Right
> mouse click on project (not report folder) and paste. Then rename to
> whatever you want. That gets the whole report including the dataset.
> Not very discoverable. I would expect to be able to paste into the reports
> folder.
> Bruce L-C
> "Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
> news:%23p5b5pcmEHA.952@.TK2MSFTNGP14.phx.gbl...
>> I copy a report by viewing the code and pasting into a new report. The
>> problem is that the fields do not display for expressions etc. It gives
> the
>> following error "report item not linked to a dataset". How do I link the
>> report to a dataset so I can see the list of available dataset fields?
>> Thanks
>>
>|||Yes, create a dataset (I have to do this anytime I use the wizard because I
don't like the name the wizard gives and I change the name of the dataset).
Then click on the table/matrix, whatever the dataset is bound to,
properties, datasetname.
Bruce L-C
"Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
news:ebmM3HmmEHA.392@.tk2msftngp13.phx.gbl...
> Is there anyway to link the report to a dataset if I did it the other way?
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OXYb8HdmEHA.1672@.TK2MSFTNGP14.phx.gbl...
> > Copy and paste the report instead. Right mouse click on report, copy.
> > Right
> > mouse click on project (not report folder) and paste. Then rename to
> > whatever you want. That gets the whole report including the dataset.
> >
> > Not very discoverable. I would expect to be able to paste into the
reports
> > folder.
> >
> > Bruce L-C
> >
> > "Ed Willis" <ed_willis@.acsi.orgnospam> wrote in message
> > news:%23p5b5pcmEHA.952@.TK2MSFTNGP14.phx.gbl...
> >> I copy a report by viewing the code and pasting into a new report. The
> >> problem is that the fields do not display for expressions etc. It gives
> > the
> >> following error "report item not linked to a dataset". How do I link
the
> >> report to a dataset so I can see the list of available dataset fields?
> >>
> >> Thanks
> >>
> >>
> >
> >
>

Linked table with percentages

I'm using Access as a front end to a SQL table. What format do I need to
put the SQL fields in to appear as percentages in Access.What version of Access? ADP? MDB? It involves setting the Format
property in the Datasheet, Form or Report. If you have any further
problems with this issue, you'll get a wider range of responses
posting in one of the Access newsgroups since it's really an Access
issue that has nothing to do with SQL Server.
--Mary
On Wed, 1 Dec 2004 06:41:09 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:

>I'm using Access as a front end to a SQL table. What format do I need to
>put the SQL fields in to appear as percentages in Access.|||I'm in access2003. The fields in the SQL table are defined as Numeric 9
(10.4). Those same fields are appearing as Currency in Access although the
table is directly linked to SQL. I'll also post this in the Access
Newsgroup. Thanks.
"Mary Chipman" wrote:

> What version of Access? ADP? MDB? It involves setting the Format
> property in the Datasheet, Form or Report. If you have any further
> problems with this issue, you'll get a wider range of responses
> posting in one of the Access newsgroups since it's really an Access
> issue that has nothing to do with SQL Server.
> --Mary
> On Wed, 1 Dec 2004 06:41:09 -0800, "SharonInGa"
> <SharonInGa@.discussions.microsoft.com> wrote:
>
>sql

Linked table with percentages

I'm using Access as a front end to a SQL table. What format do I need to
put the SQL fields in to appear as percentages in Access.
What version of Access? ADP? MDB? It involves setting the Format
property in the Datasheet, Form or Report. If you have any further
problems with this issue, you'll get a wider range of responses
posting in one of the Access newsgroups since it's really an Access
issue that has nothing to do with SQL Server.
--Mary
On Wed, 1 Dec 2004 06:41:09 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:

>I'm using Access as a front end to a SQL table. What format do I need to
>put the SQL fields in to appear as percentages in Access.
|||I'm in access2003. The fields in the SQL table are defined as Numeric 9
(10.4). Those same fields are appearing as Currency in Access although the
table is directly linked to SQL. I'll also post this in the Access
Newsgroup. Thanks.
"Mary Chipman" wrote:

> What version of Access? ADP? MDB? It involves setting the Format
> property in the Datasheet, Form or Report. If you have any further
> problems with this issue, you'll get a wider range of responses
> posting in one of the Access newsgroups since it's really an Access
> issue that has nothing to do with SQL Server.
> --Mary
> On Wed, 1 Dec 2004 06:41:09 -0800, "SharonInGa"
> <SharonInGa@.discussions.microsoft.com> wrote:
>
>

Linked table in Access shows wrong data

I am attempting to link two tables from SQL server to an access application. Both contain the same column names and the same fields as concatenated primary key but the tables are named differently, have different values and used differently. When these ta
bles are linked in access, one of the tables changes to a single primary key. When this table is opened, you see many duplicates (of what was the concatenated primary key) but the count of records is still equal to SQL Server. When I query in Access for d
uplicates, no results are returned. I drop the table and re-link it, but it still shows a single key. How can I get Access to accept the true table schema?
"bbitzer" <anonymous@.discussions.microsoft.com> wrote in message
news:0219C2D0-5ADB-48E7-86D0-EAC93D9D5422@.microsoft.com...
> I am attempting to link two tables from SQL server to an access
application. Both contain the same column names and the same fields as
concatenated primary key but the tables are named differently, have
different values and used differently. When these tables are linked in
access, one of the tables changes to a single primary key. When this table
is opened, you see many duplicates (of what was the concatenated primary
key) but the count of records is still equal to SQL Server. When I query in
Access for duplicates, no results are returned. I drop the table and re-link
it, but it still shows a single key. How can I get Access to accept the true
table schema?<
On the surface it sounds like a bug... make sure you update your client with
the latest MDAC and Jet updates. Also, on the tables you mention on SQL
Server, be sure that you have added an additional column of data type
timestamp.
Steve

Linked SQL Server 2000 Table Displaying -1 Instead of 1 for Binary

Hello all - I have something strange going on with a linked SQL Server 2000
table in Access 97. One of the fields in the table is a Binary data type.
When viewing the table in Access, I see -1 instead of a 1 for this field in
the table (even though it correctly displays 1s where approproate when viewed
from Enterprise Manager). I was about to convert the field to a TINYINT in
SQL, but figured I should try to understand the behavior instead of
circumventing it!
Any suggestions would be greatly appreciated! Thanks...
...Doug
Do you mean bit field?
If so, SQL Server treats a true bit as 1 and false as 0. Access and VB
treat true as
-1 and false as 0.
"pittpanther" wrote:

> Hello all - I have something strange going on with a linked SQL Server 2000
> table in Access 97. One of the fields in the table is a Binary data type.
> When viewing the table in Access, I see -1 instead of a 1 for this field in
> the table (even though it correctly displays 1s where approproate when viewed
> from Enterprise Manager). I was about to convert the field to a TINYINT in
> SQL, but figured I should try to understand the behavior instead of
> circumventing it!
> Any suggestions would be greatly appreciated! Thanks...
> ...Doug
sql