Jump to URL's

I have a report that needs to "jump" to 2 URL's at the same time. I also need
them to open in a second and third window leaving the first open to the
orginal report. Can this be done? I have the report right now that can "jump"
to one of the links in the cell. But it uses the original window.TTT
"Robert B." wrote:
> I have a report that needs to "jump" to 2 URL's at the same time. I also need
> them to open in a second and third window leaving the first open to the
> orginal report. Can this be done? I have the report right now that can "jump"
> to one of the links in the cell. But it uses the original window.sql

JOINs and SUMs Not Giving Expected Results

I have just added a third table to a query and I am no longer getting
the results I am expecting.

Three Tables:
CUSTINVOICEJOUR (Header Table)
CUSTINVOICETRANS (Line Item Table)
MARKUPTRANS (Additional Header Info)

CUSTINVOICEJOUR has a one to many relationship to CUSTINVOICETRANS.

CUSTINVOICEJOUR has a one to many relationship to MARKUPTRANS.

I need to sum an integer column from MARKUPTRANS, in rows that are
related to CUSTINVOICEJOUR, and include that output with my query
below, which right now has a row for each CUSTINVOICETRANS record:

SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY
FROM CUSTINVOICEJOUR INNER JOIN
CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =
CUSTINVOICETRANS.INVOICEID
WHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm')

The above works fine - a row for each record in CUSTINVOICETRANS with
the header info in there as well.

I tried the query below to add a SUM() from MARKUPTRANS, but when I run
it, I get one row with strange results in it - not what I expected.
What am I doing wrong?

SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,
SUM(MARKUPTRANS.VALUE) AS FreightValue
FROM CUSTINVOICEJOUR INNER JOIN
CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =
CUSTINVOICETRANS.INVOICEID INNER JOIN
MARKUPTRANS ON CUSTINVOICEJOUR.RECID =
MARKUPTRANS.TRANSRECID
WHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm') AND (MARKUPTRANS.DATAAREAID =
'acm')
GROUP BY CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,
MARKUPTRANS.MARKUPCODE
HAVING (MARKUPTRANS.MARKUPCODE = 'Freight')"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message news:ci581o$h9v@.odak26.prod.google.com...
> I have just added a third table to a query and I am no longer getting
> the results I am expecting.
<snip
If you're aggregating a column from MARKUPTRANS, then you shouldn't include another column from that same table in the
GROUP BY clause. I would rewrite that as a correlated subquery in the SELECT list.

SELECT
CUSTINVOICEJOUR.INVOICEAMOUNT,
CUSTINVOICETRANS.QTY,
(Select SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE CUSTINVOICEJOUR.RECID = MARKUPTRANS.TRANSRECID
and MARKUPTRANS.DATAAREAID = 'acm'
and MARKUPTRANS.MARKUPCODE = 'Freight' ) AS FreightValue

FROM
CUSTINVOICEJOUR
INNER JOIN
CUSTINVOICETRANS
ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID
WHERE
(CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm') ;

--
Paul Horan[TeamSybase]

Joining two tables multiple times

Hi
I have two tables (in an third party application, I cannot change the
data structure) as follows:-
T1 - Main data
Amount Ref1 Ref2 Ref3
==============================
100 A A A
150 A B A
200 A B B
T2 - Reference data
Type Value Name
=========================
Ref1 A Area 1
Ref1 B Area 2
...
Ref2 A Dept 1
Ref2 B Dept 2
...
Ref3 A Type 1
Ref3 B Type 3
At a simple level I want to be able to return (though obviously there
are many more complex applications of this data that I want to do):-
Amount Name1 Name2 Name3
==================================
100 Area 1 Dept 1 Type 1
150 Area 1 Dept 2 Type 1
200 Area 1 Dept 2 Type 2
At the moment I achive this by creating a query for each Refn type
Select * FROM T2
WHERE Type = 'Refn'
and then joining T1 several types to each of these queries. Is there a
way of creating this without creating the queries first?
App is SQL server, I'm using Access 2000 to query, but quite happy (and
permitted) to use passthrough queries instead.
Any help gratefully received!
MattTry this
create table #T1(Amount int, Ref1 char(1),Ref2 char(1),Ref3 char(1))
insert into #T1(Amount,Ref1,Ref2,Ref3) values(100,'A','A','A')
insert into #T1(Amount,Ref1,Ref2,Ref3) values(150,'A','B','A')
insert into #T1(Amount,Ref1,Ref2,Ref3) values(200,'A','B','B')
create table #T2(Type char(4),Value char(1), Name varchar(10))
insert into #T2(Type,Value,Name) values ('Ref1','A','Area 1')
insert into #T2(Type,Value,Name) values ('Ref1','B','Area 2')
insert into #T2(Type,Value,Name) values ('Ref2','A','Dept 1')
insert into #T2(Type,Value,Name) values ('Ref2','B','Dept 2')
insert into #T2(Type,Value,Name) values ('Ref3','A','Type 1')
insert into #T2(Type,Value,Name) values ('Ref3','B','Type 2')
select T1.Amount, t2a.Name as Name1, t2b.Name as Name2, t2c.Name as
Name3
from #T1 as T1
inner join #T2 as t2a on t2a.Type='Ref1' and T1.Ref1=t2a.Value
inner join #T2 as t2b on t2b.Type='Ref2' and T1.Ref2=t2b.Value
inner join #T2 as t2c on t2c.Type='Ref3' and T1.Ref3=t2c.Value
drop table #T1
drop table #T2|||markc600@.hotmail.com wrote:
> Try this
> [snipped]
> select T1.Amount, t2a.Name as Name1, t2b.Name as Name2, t2c.Name as
> Name3
> from #T1 as T1
> inner join #T2 as t2a on t2a.Type='Ref1' and T1.Ref1=t2a.Value
> inner join #T2 as t2b on t2b.Type='Ref2' and T1.Ref2=t2b.Value
> inner join #T2 as t2c on t2c.Type='Ref3' and T1.Ref3=t2c.Value
> drop table #T1
> drop table #T2
Mark
Many thanks for such a swift reply - just what I needed. Turned the
real world SQL into this (which worked a treat).
Matt
SELECT T1.CODE, T1.NAME AS COSTCENTRE, T2.NAME AS FUNCTION, T3.NAME AS
REGION, T4.NAME AS LNHREGION, T8.NAME AS BRANCH
FROM SADFLDGRIP AS DATA
INNER JOIN
SSRFACC AS CA ON
CA.SUN_DB = 'RIP'
AND
DATA.ACCNT_CODE = CA.ACCNT_CODE
INNER JOIN
SSRFANV AS T1 ON T1.CATEGORY = 'T1'
AND
T1.SUN_DB = 'RIP'
AND
DATA.ANAL_T1 = T1.CODE
INNER JOIN
SSRFANV AS T2 ON T2.CATEGORY = 'T2'
AND
T2.SUN_DB = 'RIP'
AND
DATA.ANAL_T2 = T2.CODE
INNER JOIN
SSRFANV AS T3 ON T3.CATEGORY = 'T3'
AND
T3.SUN_DB = 'RIP'
AND
DATA.ANAL_T3 = T3.CODE
INNER JOIN
SSRFANV AS T4 ON T4.CATEGORY = 'T4'
AND
T4.SUN_DB = 'RIP'
AND
DATA.ANAL_T4 = T4.CODE
INNER JOIN
SSRFANV AS T8 ON T8.CATEGORY = 'T8'
AND
T8.SUN_DB = 'RIP'
AND
DATA.ANAL_T8 = T8.CODE
WHERE DATA.PERIOD >= 2004001 AND DATA.PERIOD <=2005001 AND
CA.ACCNT_TYPE = 'P'
GROUP BY T1.CODE, T1.NAME, T2.NAME, T3.NAME, T4.NAME, T8.NAME
;|||you can try this 3 solutions
(i let myself redefine and rename some of your tables and columns):
SET NOCOUNT ON;
SET ANSI_NULLS ON;
USE YOUR_DB;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='MainData') DROP TABLE MainData;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='RefData') DROP TABLE RefData;
CREATE TABLE MainData(
amnt INTEGER NOT NULL,
ref1 CHAR(1) NOT NULL CHECK(ref1 IN('A', 'B')),
ref2 CHAR(1) NOT NULL CHECK(ref2 IN('A', 'B')),
ref3 CHAR(1) NOT NULL CHECK(ref3 IN('A', 'B')));
CREATE INDEX MainData_ref1_Idx ON MainData(ref1);
CREATE INDEX MainData_ref2_Idx ON MainData(ref2);
CREATE INDEX MainData_ref3_Idx ON MainData(ref3);
INSERT INTO MainData
SELECT 100, 'A', 'A', 'A' UNION ALL
SELECT 150, 'A', 'B', 'A' UNION ALL
SELECT 200, 'A', 'B', 'B';
CREATE TABLE RefData(
ref_tp CHAR(4) NOT NULL,
ref_vl CHAR(1) NOT NULL CHECK(ref_vl IN('A', 'B')),
ref_nm CHAR(6) NOT NULL);
CREATE INDEX RefData_ref_tp_Idx ON RefData(ref_tp);
INSERT INTO RefData
SELECT 'Ref1','A','Area 1' UNION ALL
SELECT 'Ref1','B','Area 2' UNION ALL
SELECT 'Ref2','A','Dept 1' UNION ALL
SELECT 'Ref2','B','Dept 2' UNION ALL
SELECT 'Ref3','A','Type 1' UNION ALL
SELECT 'Ref3','B','Type 2';
-- amnt ref_nm1 ref_nm2 ref_nm3
-- 100 Area 1 Dept 1 Type 1
-- 150 Area 1 Dept 2 Type 1
-- 200 Area 1 Dept 2 Type 2
SELECT amnt,
(SELECT R1.ref_nm FROM RefData AS R1
WHERE R1.ref_tp = 'Ref1' AND R1.ref_vl = M.ref1) AS ref_nm1,
(SELECT R2.ref_nm FROM RefData AS R2
WHERE R2.ref_tp = 'Ref2' AND R2.ref_vl = M.ref2) AS ref_nm2,
(SELECT R3.ref_nm FROM RefData AS R3
WHERE R3.ref_tp = 'Ref3' AND R3.ref_vl = M.ref3) AS ref_nm3
FROM MainData AS M
SELECT M.amnt,
R1.ref_nm AS ref_nm1, R2.ref_nm AS ref_nm2, R3.ref_nm AS ref_nm3
FROM MainData AS M, RefData AS R1, RefData AS R2, RefData AS R3
WHERE R1.ref_tp = 'Ref1' AND R1.ref_vl = M.ref1
AND R2.ref_tp = 'Ref2' AND R2.ref_vl = M.ref2
AND R3.ref_tp = 'Ref3' AND R3.ref_vl = M.ref3
SELECT M.amnt,
R1.ref_nm AS ref_nm1, R2.ref_nm AS ref_nm2, R3.ref_nm AS ref_nm3
FROM RefData AS R3
RIGHT OUTER JOIN RefData AS R2
RIGHT OUTER JOIN RefData AS R1
RIGHT OUTER JOIN MainData AS M
ON R1.ref_vl = M.ref1 AND R1.ref_tp = 'Ref1'
ON R2.ref_vl = M.ref2 AND R2.ref_tp = 'Ref2'
ON R3.ref_vl = M.ref3 AND R3.ref_tp = 'Ref3'
-- WHERE R1.ref_tp = 'Ref1'
-- AND R2.ref_tp = 'Ref2'
-- AND R3.ref_tp = 'Ref3'

Joining two dropdown lists to create third dropdownlist

How do i add two values of Dropdownlist 1 and 2 to create No.3

Is there an easy way through Visual Studio to do this than Realms of code that i have been reading online,

Surely, there is some command that is like Select Distinct Name from Clients WHEREcity=@.city +country=@.country

Or is it not this easy?

For multiple conditions you would need to use AND.

Select Distinct Name from Clients WHEREcity=@.city ANDcountry=@.country.

|||

Thanks,

The statement "Select Distinct Name from Clients WHEREcity=@.city ANDcountry=@.country." works fine in the query builder but how do i link Dropdownlist 2 to it.

I have tried all different ways in the Define Paramers Option in Dropdownlist 3 but have had no luck.

Any help would be appreciated.

I am working through VStudio2005 and am new to programming so am building dropdownlists from the toolbox etc.

Thanks,

Sully

Join on a third table

I currently have a join on 2 tables. An abbreviation of the code is as follow:

sql = "SELECT pd.RaceID, ir.EventPl, ir.RacePl FROM PartData pd INNER JOIN IndResults ir ON pd.PartID = ir.PartID WHERE pd.RaceID = " & lRaceID & " ORDER By EventPl"

Here's the problem: I need to change the WHERE clause to a field value from another table. How do I incorporate a join with a third table?

Thanks!select T1.*, T3.*
from T1 join T2 on T1.id=T2.id join T3 on T2.altid = T3.id|||...do I need to use JOIN as opposed to INNER JOIN? Also, do I need to give T2 and altid?

Thanks!|||Have you gone thru JOINS topic in books online, has got code examples so.|||I will do that!