Help with select 1 only

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I got a problem with this query, it always come out 1 record but it's
include the other record with diffrent case_no ID. For example I search case
number=20==> the result is:
caseno1 mon_ltr_sent mon_ltr_rtn caseno2 inv_ltr_sent inv_ltr_rtn
20 11/11/2000 12/12/2000 1 2/2/2004 2/3/2004

Thanks in advange.

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter, dbo_letter2
WHERE (((dbo_letter.case_no)=([Forms]![frmCaseSearch].[CaseNoBox]))) or
(((dbo_lette2r.case_no)=([Forms]![frmCaseSearch].[CaseNoBox])));
 
MN,

From your SQL, it looks like you are trying to join the two tables when
dbo_letter.case_no = dbo_letter2.case_no.

If that is what you are trying to do, then your SQL wants to look like:

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter as L1 INNER JOIN dbo_letter2 as L2
ON L1.case_no = L2.case_no
WHERE dbo_letter.case_no=[Forms]![frmCaseSearch].[CaseNoBox];

Alternatively, you can keep the SQL that you have and change the 'or' to
'and'. This would look like:

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter, dbo_letter2
WHERE (((dbo_letter.case_no)=([Forms]![frmCaseSearch].[CaseNoBox]))) AND
(((dbo_lette2r.case_no)=([Forms]![frmCaseSearch].[CaseNoBox])));

The 'AND' will force the records from both tables to have the same case_no
value.

Hope I understood correctly.
 
Thank for reply,
But, I am not trying to join 2 table by case_no I want to find whatever
table contain case_no=[Forms]![frmCaseSearch].[CaseNoBox] ==> (for example
case=20)
I tried using AND but the result is 0, white table dbo_letter have 1 record
(!)??
Some case_no they are either in dbo_letter or dbo_letter2. ?
Again thank for taking your time,
Regards
MN
Chaim said:
MN,
From your SQL, it looks like you are trying to join the two tables when
dbo_letter.case_no = dbo_letter2.case_no.

If that is what you are trying to do, then your SQL wants to look like:

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter as L1 INNER JOIN dbo_letter2 as L2
ON L1.case_no = L2.case_no
WHERE dbo_letter.case_no=[Forms]![frmCaseSearch].[CaseNoBox];

Alternatively, you can keep the SQL that you have and change the 'or' to
'and'. This would look like:

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter, dbo_letter2
WHERE (((dbo_letter.case_no)=([Forms]![frmCaseSearch].[CaseNoBox]))) AND
(((dbo_lette2r.case_no)=([Forms]![frmCaseSearch].[CaseNoBox])));

The 'AND' will force the records from both tables to have the same case_no
value.

Hope I understood correctly.
--
Chaim


MN said:
Hi,
I got a problem with this query, it always come out 1 record but it's
include the other record with diffrent case_no ID. For example I search case
number=20==> the result is:
caseno1 mon_ltr_sent mon_ltr_rtn caseno2 inv_ltr_sent inv_ltr_rtn
20 11/11/2000 12/12/2000 1 2/2/2004 2/3/2004

Thanks in advange.

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter, dbo_letter2
WHERE (((dbo_letter.case_no)=([Forms]![frmCaseSearch].[CaseNoBox]))) or
(((dbo_lette2r.case_no)=([Forms]![frmCaseSearch].[CaseNoBox])));
Click to expand...
Click to expand...
 
MN,

If I am understanding correctly this time, I think you need something along
these lines:

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter as L
UNION
SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter2 as L2
WHERE (((dbo_letter.case_no)=([Forms]![frmCaseSearch].[CaseNoBox]))) or
(((dbo_lette2r.case_no)=([Forms]![frmCaseSearch].[CaseNoBox])));

This will get all of the records from dbo_letter that match
Forms!frmCaseSearch.CaseNoBox, whether there is a matching record in
dbo_letter2 or not and union that set with the set from dbo_letter2.

I doubt that you can use a UNION in a select ... into. You might want to
create an empty TBLTempLetter table and then do an
insert into TBLTempLetter ( field list )
select ....

where the select .... is the UNION SELECT statement above.

Good luck.
--
Chaim


MN said:
Thank for reply,
But, I am not trying to join 2 table by case_no I want to find whatever
table contain case_no=[Forms]![frmCaseSearch].[CaseNoBox] ==> (for example
case=20)
I tried using AND but the result is 0, white table dbo_letter have 1 record
(!)??
Some case_no they are either in dbo_letter or dbo_letter2. ?
Again thank for taking your time,
Regards
MN
Chaim said:
MN,
From your SQL, it looks like you are trying to join the two tables when
dbo_letter.case_no = dbo_letter2.case_no.

If that is what you are trying to do, then your SQL wants to look like:

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter as L1 INNER JOIN dbo_letter2 as L2
ON L1.case_no = L2.case_no
WHERE dbo_letter.case_no=[Forms]![frmCaseSearch].[CaseNoBox];

Alternatively, you can keep the SQL that you have and change the 'or' to
'and'. This would look like:

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter, dbo_letter2
WHERE (((dbo_letter.case_no)=([Forms]![frmCaseSearch].[CaseNoBox]))) AND
(((dbo_lette2r.case_no)=([Forms]![frmCaseSearch].[CaseNoBox])));

The 'AND' will force the records from both tables to have the same case_no
value.

Hope I understood correctly.
--
Chaim


MN said:
Hi,
I got a problem with this query, it always come out 1 record but it's
include the other record with diffrent case_no ID. For example I search case
number=20==> the result is:
caseno1 mon_ltr_sent mon_ltr_rtn caseno2 inv_ltr_sent inv_ltr_rtn
20 11/11/2000 12/12/2000 1 2/2/2004 2/3/2004

Thanks in advange.

SELECT dbo_letter.case_no AS caseno1, dbo_letter.mon_ltr_sent,
dbo_letter.mon_ltr_rtn, dbo_letter2.case_no AS caseno2,
dbo_letter2.inv_ltr_sent, dbo_letter2.inv_ltr_rtn
INTO TBLTempLetter
FROM dbo_letter, dbo_letter2
WHERE (((dbo_letter.case_no)=([Forms]![frmCaseSearch].[CaseNoBox]))) or
(((dbo_lette2r.case_no)=([Forms]![frmCaseSearch].[CaseNoBox])));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I need to help with Excel (beginner) -- year row and month column 2
Help with input box to select a sort up to 28 1
help with reports 1
VLOOKUP with 2 criteria 1
Help with Advanced Filter Comparison Criteria 2
Please help with formula (multiple ifs to determine due date) 3
Help with Dataview 1
help with table design required 4

Back
Top