473,699 Members | 2,226 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 3998
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
4281
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
1818
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
4473
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
1685
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
2066
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
2530
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
5335
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
3922
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
27720
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
8618
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9178
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8916
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,...
1
6534
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5875
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
4376
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
4631
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2348
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2010
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.