Dear Maria:
Perhaps then you are relying on a supposed "physical order" of the
rows. This is imaginary. Here today and gone tomorrow. There will
be no reliable way to accomplish anything on this basis. Without a
basis, you have nothing.
Sorry to be the bearer of bad tidings. The ability to relate rows
between tables is fundamental to relational databases and must be
designed into the database before proceeding to any other phase of
development, or you will be building a house of cards.
Sorry again! My early mistakes in this thread were based on the
presumption that such relationships existed, making some solution
possible.
If a physical order currently exists and is reliable, and if there is
significant data to be worth an effort to save it, then I strongly
recommend creating such a relationship while it may be saved. This
could be done by coding VBA using a recordset to step through the rows
of the table in physical order, copying the Invoice Number (or
whatever common key is available and desirable) from the BIG rows to
the subsequent IT1 rows. I recommend an immediate backup of the data
because the physical order may be disturbed at any time.
Database engines do not concern themselves with maintaining any
physical order, and you must not rely on this. Creating relationships
is a key concept, along with having an appropriate table design so
relationships can be enforced.
Finally, no query can be designed to work on the physical order of a
table.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Dear Tom,
The first statement is correct :
"If a row with 'BIG' in is followed by a row with 'IT1' in. Then
the rows with 'IT1' in will inherit the data in columns "Inv No" and
"Invoice Date" where Field1 = 'BIG'."
However there is no sort order. There is no column which sorts the rows in
the table which produces the order defined!
In Field1 the value 'BIG' may be followed by one instance of 'IT1' or five
instances of it!
Look forward to your suggestions!
Many Thanks
Maria
:
Dear Maria:
You said:
"If a row with 'BIG' in is followed by a row with 'IT1' in. Then
the rows with 'IT1' in will inherit the data in columns "Inv No" and
"Invoice Date" where Field1 = 'BIG'."
In what sort order does this occur? By which column(s) can you sort
the rows in the table to produce the order under which this definition
applies?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Wed, 29 Sep 2004 02:39:04 -0700, "Repeat records"
Dear Tom,
Thanks for your reply. I know it is rather impossible to find a solution.
To answer your question - How do you propose to know which 'BIG' row is
associated with each 'IT1' row.
If a row with 'BIG' in is followed by a row with 'IT1' in. Then the rows
with 'IT1' in will inherit the data in columns "Inv No" and "Invoice Date"
where Field1 = 'BIG'.
Example:
Field1 Price Quantity Invoiced Inv No Invoice Date
BIG 3556 1/12/2003
IT1 2.99 10 3556 1/12/2003
IT1 1.99 15 3556 1/12/2003
Would appreciate any advice?!?
Many Thanks
Maria
:
Dear Maria:
Both your problems are my mistake. Start with the first, however.
You have rows in your table that are "BIG" rows and rows that are
"IT1" rows. I had assumed there must be some basis for knowing which
IT1 rows are associated with each BIG row. Having the same [Inv No]
value in each would seem a natural way of doing this. However, a
review of your original post reveals this is not the case. It seems
there is no way to tell with which BIG row each IT1 rows is
associated.
The latter problem is meaningless without a solution to this first
problem. How do you propose to know which BIG row is associated with
each IT1 row?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 28 Sep 2004 04:21:05 -0700, "Repeat records"
Dear Tom,
Thank you very much for your prompt response which I am trying.
The first bit :
SELECT *
FROM [SELECT * FROM Maria WHERE Field1 = "BIG"]. AS T1 INNER JOIN [SELECT *
FROM Maria WHERE Field1 = "IT1"]. AS T2 ON T2.[Inv No] = T1.[Inv No];
When this query is run no data is output.
Using the UNION ALL query:
SELECT *
FROM [SELECT * FROM Maria WHERE Field1 = "BIG"]. AS T1 INNER JOIN [SELECT *
FROM Maria WHERE Field1 = "IT1"]. AS T2 ON T2.[Inv No] = T1.[Inv No]
UNION ALL SELECT * FROM Maria WHERE Field1 = "BIG";
When I run this I get an error message stating: The number of columns in
the two selected tables or queries of a union query do not match.
Where am I going wrong with this UNION ALL statement??
Please advise.
Many Thanks
Maria
:
Dear Maria:
I looks like you only need to JOIN the table with itself on the [Inv
No], filtering the two sets of records first:
SELECT *
FROM
(SELECT * FROM Maria WHERE Field1 = 'BIG') T1
INNER JOIN (
(SELECT * FROM Maria WHERE Field1 = 'IT1') T2
ON T2.[Inv No] = T1.[Inv No]
Change the SELECT clause to list only those columns desired.
This assumes the [Inv No] column is unique within the set of all rows
where Field1 = BIG.
To get exactly what you propose, create a UNION ALL of the rows from
the above with the rows where Field1 = BIG to get exactly what you
proposed.
This looks very much as though there are two tables in one, where the
IN1 rows belong to a separate, dependent table on the "many" side of a
one-to-many relationship. If so, the database would be much more
normalized and would function better with this as 2 tables. I notice
that the BIG rows do not have Price or [Quantity Invoiced] while the
IT1 rows do not have [Inv No] or [Invoice Date]. This is a pretty
clear indication of the problem I described.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Mon, 27 Sep 2004 09:19:04 -0700, "Repeat records"
I have a table called Maria.
With the following fields in: Field1,Price,Quantity Invoiced,Inv No, Invoice
Date
Field1 Price Quantity Invoiced Inv No Invoice Date
BIG 3555 24/10/2003
IT1 1.99 5
BIG 3556 1/12/2003
IT1 2.99 10
IT1 1.99 15
For every instance in Field1 where 'BIG' is followed by 'IT1' I want to copy
the value of the records in fields Inv No and Invoice Date. This will leave
me with the following table:
Field1 Price Quantity Invoiced Inv No Invoice Date
BIG 3555 24/10/2003
IT1 1.99 5 3555 24/10/2003
BIG 3556 1/12/2003
IT1 2.99 10 3556 1/12/2003
IT1 1.99 15 3556 1/12/2003
How do I do this using a query(using code)???
Manually I can use CTRL + (') but I have thousands of records.
Please Help!
Manage Your Profile
©2004 Microsoft Corporation. All rights reserved. Terms of Use |Trademarks
|Privacy Statement
