473,700 Members | 2,524 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

problem query

Hi all, I have an invoice table query that returns 10 records. ie. there
are 10 invoices.

When I try to join a subjects table to retrieve the subject name
associated with an invoice it returns 11 records.

I know this is because for each invoice, there might be multiple
subjects. So when I join the subject table to get the subject's name, it
will add an extra record.

How can I get the query to return only the 10 records, but for that
single record that has 2 subjects, to show both subjects in the same
field for that record?

This query gets 11 records:
*************** *************** *********
select invoiceid,
subject.name,
files.file_numb er
from invoices
inner join files on files.file_numb er = invoices.file_n umber
inner join subject on subject.file_nu mber = files.file_numb er
where invoices.invoic eID between 3173 and 3183
order by invoiceid
*************** *************** ************

So instead of having the results look like this:

invoiceID name file_number
3173 jon 22222
3173 jane 22222

I would like:

invoiceID name file_number
3173 jon and jane 22222

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
2 2176
You have two options

1) recreate the record with the duplicate ID where you include both
names in the name field and delete the 2nd record

2) assign a different ID to the 2nd record with the duplicate ID. I
will guess that your ID column is numeric and not alpha numeric. If it
were/is alpha numeric then you could do something like

3173 - 1
3173 - 2

else the 2nd 3173 gets some totally different number.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2
Hammy Hammy <ch***@thehams. ca> wrote in
news:3f******** *************** @news.frii.net:
Hi all, I have an invoice table query that returns 10 records.
ie. there are 10 invoices.

When I try to join a subjects table to retrieve the subject
name associated with an invoice it returns 11 records.

I know this is because for each invoice, there might be
multiple subjects. So when I join the subject table to get the
subject's name, it will add an extra record.

How can I get the query to return only the 10 records, but for
that single record that has 2 subjects, to show both subjects
in the same field for that record?

You can't do that with a query. There is no join that will merge
multiple rows into a single one.

What you /can/ do is create a form on the main query, which does
not have the subjects table and include the subjects information on
a subform control. Besides queries should never be used for user
interface. Always encapsulate them in a form or report.

Bob Q.

This query gets 11 records:
*************** *************** *********
select invoiceid,
subject.name,
files.file_numb er
from invoices
inner join files on files.file_numb er = invoices.file_n umber
inner join subject on subject.file_nu mber = files.file_numb er
where invoices.invoic eID between 3173 and 3183
order by invoiceid
*************** *************** ************

So instead of having the results look like this:

invoiceID name file_number
3173 jon 22222
3173 jane 22222

I would like:

invoiceID name file_number
3173 jon and jane 22222

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3046
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going on? I have tested this extensively and can say for certain that installing this hot fix is what has caused the performance problem. I just don't know why or how to fix it. Brian Oster
8
19593
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a couple of tables in my database using INNER JOINS and the WHERE clause to specify the required constraints. However, I also want to read two fields from a *single* record from a table called 'Locations' and then apply one of these field's values...
4
7744
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had create a query by making relation to a table & query, but I can't update record in query or in form. I believe the problem is due to the source query. In source query, there is a filter to show the incomplete record ("is null" in delivery date)], but I need to re-use the job no. if the job is...
6
6547
by: lenny | last post by:
Hi, I've been trying to use a Sub or Function in VBA to connect to a database, make a query and return the recordset that results from the query. The connection to the database and the query works fine, but passing the resulting recordset back to the sub's caller is not working out.
3
3430
by: StBond | last post by:
Hi everyone, I am new to Access and Visual Basic so things my be getting across a bit cloudy. I only started using VB for one week. I am having a little problem with the database that I am working on. I am working with MS Access 2002. And I am having a problem with one of my charts. I will explain how everything is laid out then go into details.
3
4610
by: Andy_Khosravi | last post by:
I have been trying to build a user friendly search engine for a small database I have created. I'm having some particular problems with one of my date fields. Here's the setup: I'm using Access 97 (I know it's old. But, it's the tool they give me to work with) My working knowledge of SQL is on the low side. My working knowledge of VBA is beginner.
20
2501
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using insert-statements. after initalizing and starting postgres 7.4 on a different port and datadirectory, i tried to import the sql-dump with the copy statements. this import fails, but importing the dump-file with inserts took a long time but was...
0
2211
by: mjsterz | last post by:
I've been working with VB .NET for less than a year and this is the first time I've posted on one of these groups, so let me apologize beforehand if I'm being unclear, not posting my issue correctly, posting to the wrong forum, or committing some other sort of faux pas. My team is developing a Windows Forms application using VS 2005 with SQL Server 2005 on the back end and we're having a problem using ComboBoxes data bound to lookup...
3
6088
by: Juan Antonio Villa | last post by:
Hello, I'm having a problem replicating a simple database using the binary log replication, here is the problem: When the master sends an update to the slave, an example update reads as follows: UPDATE MainInfo SET dAddress='38 Holland Blvd', dCity='miami', dState='FL', dZip='33000', dCountry='USA', dPhone='999987565', dNum='AC15857', dName='Michael A Scott' WHERE did=22'
9
5758
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just call it Express for simplicity). I have, to try to simplify things, put the exact same DB on two systems, one running MSDE and one running Express. Both have 2 Ghz processors (one Intel, one AMD), both have a decent amount of RAM (Intel system...
0
8712
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9058
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8952
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8911
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4395
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4649
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3082
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2375
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2018
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.