473,856 Members | 1,836 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Joining Multiple Rows Into One Row

19 New Member
Hi all!
I am having trouble with joining multiple rows into one row. I will appreciate any help.

For columns in the query, I have: invID(autot number), entryDate, invDate, vendor, invoiceAmount from table named tblInvoice, and building, account, percent (allocation in percentage for each building) from table named tblAllocation.

This is how the table looks like:
InvID / entryDate / invDate / vendor / invoiceAmount / building / account / percent
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 / 1 / eBay / 0.5
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 /2 /BushBeans/ 0.25
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Wal-Mart / 0.7
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Target / 0.3

I want to make all the fields from same InvID in one row, so the above example would be like this:

InvID / entryDate / invDate / vendor / invoiceAmount / building1 / account1 / percent1 / building2 / account2 / percent2 / building3 / account3 / percent3/
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 / 1 / eBay / 0.5 /2 /BushBeans/ 0.25 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Wal-Mart / 0.7 / 1 /Target / 0.3

I know SQL, DAO, and VBA a little. It seems like I need to combine multiple functions, but I am having hardtime figureing out....
Aug 17 '07 #1
18 4022
ADezii
8,834 Recognized Expert Expert
Hi all!
I am having trouble with joining multiple rows into one row. I will appreciate any help.

For columns in the query, I have: invID(autot number), entryDate, invDate, vendor, invoiceAmount from table named tblInvoice, and building, account, percent (allocation in percentage for each building) from table named tblAllocation.

This is how the table looks like:
InvID / entryDate / invDate / vendor / invoiceAmount / building / account / percent
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 / 1 / eBay / 0.5
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 /2 /BushBeans/ 0.25
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Wal-Mart / 0.7
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Target / 0.3

I want to make all the fields from same InvID in one row, so the above example would be like this:

InvID / entryDate / invDate / vendor / invoiceAmount / building1 / account1 / percent1 / building2 / account2 / percent2 / building3 / account3 / percent3/
2 / 8/15/07 / 8/1/07 / ABC / $1,000.00 / 1 / eBay / 0.5 /2 /BushBeans/ 0.25 /3 /BestBuy/ 0.25
3 / 8/16/07 / 8/2/07 / CCC / $2,000.00 / 1 /Wal-Mart / 0.7 / 1 /Target / 0.3

I know SQL, DAO, and VBA a little. It seems like I need to combine multiple functions, but I am having hardtime figureing out....
For what possible reason would you want to use this logic?
Aug 17 '07 #2
Apple001
19 New Member
For what possible reason would you want to use this logic?
ADezii,
thank you for your reply. I want to use this to be able to make labels function in report in Access... To fit all the record I want in single label, I have to have all record in one row.
Aug 20 '07 #3
ADezii
8,834 Recognized Expert Expert
ADezii,
thank you for your reply. I want to use this to be able to make labels function in report in Access... To fit all the record I want in single label, I have to have all record in one row.
This is little more complex than I think you realize:
  • Is there a Maximum Number of the same Inventory IDs? If there is, what is this Number?
  • Do the following Fields already exist?
    Expand|Select|Wrap|Line Numbers
    1. building2 / account2 / percent2 
    2. building3 / account3 / percent3 
    3. building4 / account4 / percent4 
    4. building5 / account5 / percent5 
    5. building6 / account6 / percent6 
Aug 20 '07 #4
Apple001
19 New Member
This is little more complex than I think you realize:
  • Is there a Maximum Number of the same Inventory IDs? If there is, what is this Number?
  • Do the following Fields already exist?
    Expand|Select|Wrap|Line Numbers
    1. building2 / account2 / percent2 
    2. building3 / account3 / percent3 
    3. building4 / account4 / percent4 
    4. building5 / account5 / percent5 
    5. building6 / account6 / percent6 
There is no maximum number of the same Inventory IDs, but I have never seen a label that has more than 7 rows. So, I would say maximum could be 7.

No, the fields you asked me do not exist in my database.
Aug 20 '07 #5
ADezii
8,834 Recognized Expert Expert
There is no maximum number of the same Inventory IDs, but I have never seen a label that has more than 7 rows. So, I would say maximum could be 7.

No, the fields you asked me do not exist in my database.
It can be done but it would be much easier if these Fields pre-existed rather than checking for their existence, then dynamically creating them at Runtime, when and if needed. The Fields I am referring to are:
Expand|Select|Wrap|Line Numbers
  1. building2/account2/percent2 thru building7/account7/percent7
The price you'll pay will the the overhead relating to the additional 18 Fields. Just realized that I wasn't too clear on something - these new Fields will exist in a New Table that will be created and contain in a single Row, the data for mutilple IDs.

I'm going on Vacation for a few days but when I return I'll check in with you and if you are still interested, I'll work on it for you. Someone will probably come up with a better solution by then anyway.
Aug 21 '07 #6
ADezii
8,834 Recognized Expert Expert
It can be done but it would be much easier if these Fields pre-existed rather than checking for their existence, then dynamically creating them at Runtime, when and if needed. The Fields I am referring to are:
Expand|Select|Wrap|Line Numbers
  1. building2/account2/percent2 thru building7/account7/percent7
The price you'll pay will the the overhead relating to the additional 18 Fields. Just realized that I wasn't too clear on something - these new Fields will exist in a New Table that will be created and contain in a single Row, the data for mutilple IDs.

I'm going on Vacation for a few days but when I return I'll check in with you and if you are still interested, I'll work on it for you. Someone will probably come up with a better solution by then anyway.
Apple001:
If you're still interested, let me know and I'll show you how to accomplish this.
Aug 25 '07 #7
Apple001
19 New Member
Apple001:
If you're still interested, let me know and I'll show you how to accomplish this.
Sorry for the late reply. I have been busy with other projects, but YES, I am still interested of making this work. I really appreciate your help!!
Sep 6 '07 #8
ADezii
8,834 Recognized Expert Expert
Sorry for the late reply. I have been busy with other projects, but YES, I am still interested of making this work. I really appreciate your help!!
I'll get started on a solution tomorrow but please be patient since I am very busy.
Sep 7 '07 #9
Apple001
19 New Member
I'll get started on a solution tomorrow but please be patient since I am very busy.
Ok. Thank you ADezii!
Sep 7 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
4299
by: Job Lot | last post by:
Is there anyway of Joining two or more DataTable with similar structure? I have three DataTables with following structures Data, AmountB/F, Repayments, InterestCharged and AmountC/F i want to join these tables on the basis of Date. I don't want three rows for each date, i want the values to be added for similar dates. thanx
0
1827
by: Palle Girgensohn | last post by:
Hi! A performance question: I have some tables: Tabell "public.person" Kolumn | Typ | Modifierare ------------------+--------------------------+--------------- userid | text | not null
3
4479
by: james | last post by:
Hi, I would like to get all the records from 9 tables that have the same field value in one field (it is a unique field)that is shared by all the tables. Would this method of joining work: select * from table 1, table 2, table 3 where table 1.com_field = table 2.com_field and table 2.com_field = table 3.com_field
0
1695
by: laxmiuk | last post by:
I've original version of a table called PMM (Product Material Master). Thro' a web interface, user can change that table contents. Once changed, i need to raise an ECN (Engineering CHange Note) specifying what changes happened to original PMM table whether rows are deleted, new rows are added or existing rows are modified etc. I've both old and new version of PMM tables. The difference between two PMM tables are captured in a third table...
2
2073
by: Beowulf | last post by:
The problem: I'm updating a report to be "multi-language" capable. Previously, any items that had text associated with them were unconditionally pulling in the English text. The database has always been capable of storing multiple languages for an item, however. Desired output: Given the test data below, I'd like to get the following results select * from mytestfunc(1)
1
2542
by: pmouseca | last post by:
Hello all, I have two tables, one call Assignments, the other call Grades. In Assignment, the columns are: Asn_ID, Asn_Name, Asn_MaxGrade In Grades, the columns are: Grade_ID, Grade_Assignment, Grade_Student, Grade_Value There exist a one-to-many relationship on Asn_ID = Grade_Assignment
2
5343
by: Michael | last post by:
It seems that a gridview allows us to delete only a single row at a time. How to extend this functionality to select multiple rows and delete all of the selected rows in a single stroke? just like what hotmail web UI is doing now (having the option of selecting multiple rows (using the checkbox provided) and perform a set of operations on them)
3
3936
by: nigelesquire | last post by:
Please help! I'm trying to clone and delete multiple rows with JavaScript. I need two delete buttons that work...! I only have one for now, but it's not working properly, the output count is messing up. Problems:
482
28053
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. what i am trying to display previously entered multiple fields. I am able to get my serial fields to display correctly, but i can not display my parts fields correctly. Currently this is what it does serial information 1 parts 1
0
10696
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
10780
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
10383
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
9530
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7093
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4575
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
4174
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3201
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.