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....
18  4005   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?
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.
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?
- building2 / account2 / percent2
-
building3 / account3 / percent3
-
building4 / account4 / percent4
-
building5 / account5 / percent5
-
building6 / account6 / percent6
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?
- building2 / account2 / percent2
-
building3 / account3 / percent3
-
building4 / account4 / percent4
-
building5 / account5 / percent5
-
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.
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: - 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.
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:
- 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.
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!!
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. I'll get started on a solution tomorrow but please be patient since I am very busy.
Ok. Thank you ADezii!
Sign in to post your reply or Sign up for a free account.
Similar topics | |
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
|
by: Palle Girgensohn |
last post by:
Hi!
A performance question:
I have some tables:
Tabell "public.person"
Kolumn | Typ | Modifierare
------------------+--------------------------+---------------
userid | text | not null
|
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
|
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...
|
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)
| | |
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
|
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)
|
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:
|
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
|
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,...
|
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...
| | |
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,...
|
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...
|
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...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| | |
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...
| |
