I have one table of the form:
Delivery Product
101 A
101 B
101 C
102 A
102 E
103 C
104 A
104 E
104 C
I need to update a 2nd table with the data from the first table to look
like this:
Delivery Product1 Product2 Product3
101 A B C
102 A E -
103 C - -
104 A E C
So that there is only one line of record for each delivery.
I have tried crosstab query; it doesn't give me exactly what I want -
it lists all the products for column headings (about 760) in this case.
So I will have more than 780 column headings. I need a way to update a
new table so that it looks like the 2nd table shown above: the column
headings are already there, so the query grabs the the products for a
delivery and puts them under a column heading (doesn't matter under
which field as far as it is associated with the appropriate delivery).
Thanks for your help guys. 5 1696
Seth
You can do this with recordset scanning and manipulation, of course
(you can do almost anything with recordset scanning and manipulation).
Before writing such code one would have to know the exact structure of
the second table. How many product fields are you going to allow and,
thus, create? (This question, which may be hard to answer, is why we
generally don't do what you are trying to do; if we say four product
fields then what happens when a delivery has been one of five, or
twenty-three products? [And when there is only one product many of us
are not enthusiastic about those empty fields].) As you point out, the
crosstab query gives you 780 columns which is not what you want. (Maybe
someone who is more expert at crosstabs than I will suggest a simple
solution for that!).
If I had to do this, I suppose I would write code to create the
temporary table with enough products fields to handle the maximum count
of products to delivery. But I am a compulsive code jockey; it's
unlikely that you are and therefore if I misjudged or misunderstood the
tiniest characteristic of your data, my code would probably not work;
then what?
I ask, again, for you to reconsider your needs and ask yourself is a
report (or form) showing something like
Delivery Product
--------------------------
--------------------------
101
---------------------------
A
B
C
--------------------------
--------------------------
102
---------------------------
A
E
woudn't meet your needs.
If not then if you post the exact structure of your 2nd table, I
(someone else will beat me to it, I hope) will suggest code to
accomplish what you want. You should also decide if the 2nd table is to
be a temporary or permanent table, and you should specify (you may
already have) the version of Access you are using and anything else we
should know, for example that you are using MS-SQL and not JET.
Thanks again Lyle,
I have decided to have the 2nd table have 15 product fields (about the
maximum per delivery) possible. And you are right, some of the product
fields may be empty but that is fine. I am using Access 2003. Thanks
for your help.
"Seth" <se***********@ gmail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. . I have one table of the form:
Delivery Product 101 A 101 B 101 C 102 A 102 E 103 C 104 A 104 E 104 C
I need to update a 2nd table with the data from the first table to look like this:
Delivery Product1 Product2 Product3 101 A B C 102 A E - 103 C - - 104 A E C
So that there is only one line of record for each delivery.
I have tried crosstab query; it doesn't give me exactly what I want - it lists all the products for column headings (about 760) in this case. So I will have more than 780 column headings. I need a way to update a new table so that it looks like the 2nd table shown above: the column headings are already there, so the query grabs the the products for a delivery and puts them under a column heading (doesn't matter under which field as far as it is associated with the appropriate delivery).
Thanks for your help guys.
Add a position column to your order table.
Delivery Product Position
101 A 1
101 B 2
101 C 3
102 A 1
102 E 2
103 C 1
104 A 1
104 E 2
104 C 3
You would set the position during data entry.
As a plus you can limit products per order using max position.
The cross tab would look like this
TRANSFORM First(Order.Pro duct) AS [The Value]
SELECT Order.Delivery
FROM Order
GROUP BY Order.Delivery
PIVOT Order.position In (1,2,3,4,5,6,7, 8,9,10,11,12,13 ,14,15,16);
Order 1 2 3 4 ... 16
101 A B C
102 A E
103 C
104 A C E
Well, this is hastily cobbled together code. As such it may not be
efficient and it may have errors. Obviously, it does more than you need
(such as delete and create the tables). But on my machine it creates
the Table that you describe.
Of course, as you specify that you are using Access 2003, it uses ADO.
No doubt, the champions of DAO, (almost everyone), will create and
post entirely different modules (using DAO only) that will do the job
much more quickly and in many fewer lines of code (even allowing for
the removing of table deletion, table creation and the populating of
the original table). Then you will really have SOMETHING!
My advice is to try running the code as it is (assuming you do not have
tables named FirstTable or SecondTable), and, if it works, examine
SecondTable to see if it is what you want. The code is probably
self-explanatory but you could ask any question you want about it.
Public Sub Seth()
Dim r(2) As ADODB.Recordset
Dim a As Variant
Dim z As Long
a = Array(0, 1)
For z = 0 To UBound(r)
Set r(z) = New ADODB.Recordset
r(z).ActiveConn ection = CurrentProject. AccessConnectio n
Next z
With CurrentProject. AccessConnectio n
On Error Resume Next
.Execute "DROP Table FirstTable"
.Execute "DROP Table SecondTable"
On Error GoTo 0
.Execute "CREATE TABLE FirstTable (Delivery INTEGER, Product
TEXT (10))"
.Execute "CREATE TABLE SecondTable (Delivery INTEGER)"
For z = 1 To 15
.Execute "ALTER TABLE SecondTable ADD COLUMN Product" &
CStr(z) & " TEXT (10)"
Next z
End With
With r(0)
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Open "SELECT * FROM SecondTable WHERE False"
End With
With r(1)
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Open "SELECT * FROM FirstTable WHERE False"
.AddNew a, Array(101, "A")
.AddNew a, Array(101, "B")
.AddNew a, Array(101, "C")
.AddNew a, Array(102, "A")
.AddNew a, Array(102, "E")
.AddNew a, Array(103, "C")
.AddNew a, Array(104, "A")
.AddNew a, Array(104, "E")
.AddNew a, Array(104, "C")
.UpdateBatch
.Close
.Open "SELECT DISTINCT Delivery FROM FirstTable"
While Not .EOF
r(0).AddNew Array(0), .Collect(0)
With r(2)
If .State = adStateOpen Then .Close
.Open "SELECT Product FROM FirstTable WHERE Delivery =
" & r(1).Collect(0)
While Not .EOF
r(0).Update .AbsolutePositi on, .Collect(0)
.MoveNext
Wend
End With
.MoveNext
Wend
End With
r(0).UpdateBatc h
End Sub
Thanks so much Lyle for your help. I got the number of products reduced
to less than 10 so I worked arround it (at least for now) with the
crosstab query. I haven't tried your code yet - I will let you know the
results when I try it. Thanks again for your help. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dave |
last post by:
I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.
I need to update a field in the NAMEINFO table for a particular surname in a
particular town.
I can select the records fine with this syntax (testing in Oracle SQL* Plus)
SELECT NAMEINFO.LASTNAME, NAMEINFO.FIRSTNAME, NAMEINFO.MIDDLENAME,
NAMEINFO.GENDER, ADDRESSINFO.REGION FROM NAMEINFO, ADDRESSINFO...
|
by: Lauren Quantrell |
last post by:
In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.UniqueID =
tblMyTableTEMP.UniqueID
SET
tblMyTable.myField = tblMyTableTEMP.myField,
tblMyTable.myField2 = tblMyTableTEMP.myField2,
|
by: kalamos |
last post by:
This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
by: Mike Leahy |
last post by:
Hello all,
This question is related to updating tables - is there any way to calculate or
update the values in a column in a table to the values in a field produced by
a query result? An example of what I'm trying to do is below:
update (tbl_ind_mananas LEFT JOIN (select count(*) as count, (dubicacion ||
zona || manzana) as cod_manzana from tbl_censo_poblacion_1993 group by
dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as...
| |
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that there is a threshold on the number of columns of
the table, or perhaps bytes, being updated where the engine just
decides, screw it, i'll just make a new one.
surfed this group and google, but couldn't find anything.
the context: we have some java folk who like to parametize/
|
by: PAUL |
last post by:
Hello,
I have 2 tables with a relationship set up in the dataset with vb
..net. I add a new record to the parent table then edit an existing child
record to have the new parent ID. However when I do the update the changed
parentid in the child table fails to change. No error is given its just that
the change is not written to the Database.
When I step through the records for the child table the one I would expect
to be changed has a row...
|
by: Miro |
last post by:
I will ask the question first then fumble thru trying to explain myself so i
dont waste too much of your time.
Question / Statement - Every mdb table needs a PrimaryKey ( or maybe an
index - i havnt tested the index yet ) so you
can use an .UPDATE( dataTable ) on the data adapter. Otherwise you will
get an exception error.
Is this statement true?
---- Now me fumbling thru
|
by: adithi |
last post by:
My Table Structure is:
Table A Table B Table C
colA -PK Col B-PK Col C-PK
Col B-FK Col B-FK
Col C-FK
This relation establish a Concurrent relation where in Cascade Property fails.I can set Cascade property for any two tabnles...but not the third table.
My requirement is :
|
by: Michel Esber |
last post by:
Hi all,
DB2 V8 LUW FP 15
There is a table T (ID varchar (24), ABC timestamp). ID is PK.
Our application needs to frequently update T with a new value for ABC.
update T set ABC=? where ID = ?
|
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...
| |
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: 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...
|
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: 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: 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...
|
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...
| |