By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,598 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

problem running a query

P: n/a
Hi All,

I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco

Feb 6 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 6 Feb 2007 01:15:24 -0800, Za****@gmail.com wrote:

You'll need to decide what to do with people with multiple jobs. Show
only the first one? The last one? The best paying one? The one with
the highest title (beware for ties)? etc.
Then create a query that pulls that job, and in the final query join
with it, rather than with the jobs table.

-Tom.

>Hi All,

I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco
Feb 6 '07 #2

P: n/a
Tom,

What if I just wanted to have the first instant of the person,
regardless what kind of job it shows.
How would I have to define the query. I tried LEFT, RIGHT and INNER
JOIN but no luck.
I end up with too much or too less records.

Marco

On 6 feb, 17:50, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On 6 Feb 2007 01:15:24 -0800, Zai...@gmail.com wrote:

You'll need to decide what to do with people with multiple jobs. Show
only the first one? The last one? The best paying one? The one with
the highest title (beware for ties)? etc.
Then create a query that pulls that job, and in the final query join
with it, rather than with the jobs table.

-Tom.
Hi All,
I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Feb 6 '07 #3

P: n/a
Hi Marco,
The join is probably not the issue here. Look into aggregation (min,
max, first, last, sum, group by, etc). In your query builder, look
for the sigma icon in the main menu at the top of your window.

select person, min(job) from table
group by person

that will give you one person and the minimum job name.
HTH
P

Feb 6 '07 #4

P: n/a
Hi,

u can try with the below query. However if the result set, in a real
time scenario is very heavy, then i suggest this query not be used,
since i have used "IN" as well as "Group By" clauses.

"SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID AND Jobs.JobId IN (SELECT MIN(JobId) FROM
Jobs GROUP BY Jobs.BJID)"

the Jobs.JobId i have used refers to any primary key field of the Jobs
table.
Regards,
Seema
On Feb 6, 2:15 pm, Zai...@gmail.com wrote:
Hi All,

I have a problem with running a query.

Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "

Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.

Regards
Marco

Feb 7 '07 #5

P: n/a
On 7 feb, 10:28, seemanthi...@gmail.com wrote:
Hi,

u can try with the below query. However if the result set, in a real
time scenario is very heavy, then i suggest this query not be used,
since i have used "IN" as well as "Group By" clauses.

"SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID AND Jobs.JobId IN (SELECT MIN(JobId) FROM
Jobs GROUP BY Jobs.BJID)"

the Jobs.JobId i have used refers to any primary key field of the Jobs
table.

Regards,
Seema

On Feb 6, 2:15 pm, Zai...@gmail.com wrote:
Hi All,
I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -
I tried the query but get an error message saying: syntax error.

Marco

Feb 8 '07 #6

P: n/a
On Feb 8, 10:32 am, Zai...@gmail.com wrote:
On 7 feb, 10:28, seemanthi...@gmail.com wrote:


Hi,
u can try with the below query. However if the result set, in a real
time scenario is very heavy, then i suggest this query not be used,
since i have used "IN" as well as "Group By" clauses.
"SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID AND Jobs.JobId IN (SELECT MIN(JobId) FROM
Jobs GROUP BY Jobs.BJID)"
the Jobs.JobId i have used refers to any primary key field of the Jobs
table.
Regards,
Seema
On Feb 6, 2:15 pm, Zai...@gmail.com wrote:
Hi All,
I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -

I tried the query but get an error message saying: syntax error.

Marco- Hide quoted text -

- Show quoted text -
Please check whether you have a field called JobId in your Jobs
table... if not please use the primary key column of the Jobs table
instead of JobId.

also can you please provide more details about the syntax error u have
received?

Feb 8 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.