Dear Access-Guru's
I am far from being an access specialist and i have the following
problem.
I've got an access table with individual rows and rows with subtotals.
The thing is that the rows with the subtotals contain part of what i
want to use as the key for the individual rows. Example (rows with a *
are subtotals):
12 | housing | 2.00
32 | food | 3.00
24 | equipment | 6.00
* 354 | project A | 11.00
12 | housing | 34.00
24 | equipment | 7.00
58 | tools | 3.00
71 | additional costs | 1.00
* 561 | project B | 45.00
etc.
How can i get the project number from the total-rows in the
corresponding individual rows i.e.
354 | 12 | housing | 2.00
354 | 32 | food | 3.00
354 | 24 | equipment | 6.00
561 | 12 | housing | 34.00
561 | 24 | equipment | 7.00
561 | 58 | tools | 3.00
561 | 71 | additional costs | 1.00
etc.
Thanks in advance,
Martin
6  2216 
Hi Martin.
Presumably the source table here is an import from something like a text
file. Since it depends on the order of the records in the table, it will be
important to have a primary key (e.g. a sequential AutoNumber named
ImportID) to guarantee that the records are kept in that order while you
transfer them to the real table.
1. Create a query into the temp source table - named "MyImportTable" in this
example.
2. Drag the fields you want into the grid.
3. In the Criteria row under the 2nd column (named "MyDescripField" in this
example), enter a criterion the omits the total rows. For example, if they
start with "Project ", enter:
Not Like "project *"
4. Type a subquery into a fresh column in the Field row. The subquery needs
to find the first row where the description starts with "project " and the
ImportID is greater than the ImportID on the current row. Something like
this:
ProjectNum: ( SELECT [MyFirstColumn] FROM [MyImportTable] AS Dupe
WHERE ((Dupe.[MyDescripField] Like "project *") AND (Dupe.ImportID >
[MyImportTable].ImportID))
ORDER BY ImportID )
5. After testing the query and sorting out any issues, you can turn it into
an Append query (Append on Query menu) to append the values to the real
table where you want the data.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Martin" <ma********@hotmail.com> wrote in message
news:68**************************@posting.google.c om... Dear Access-Guru's
I am far from being an access specialist and i have the following
problem.
I've got an access table with individual rows and rows with subtotals.
The thing is that the rows with the subtotals contain part of what i
want to use as the key for the individual rows. Example (rows with a *
are subtotals):
12 | housing | 2.00
32 | food | 3.00
24 | equipment | 6.00
* 354 | project A | 11.00
12 | housing | 34.00
24 | equipment | 7.00
58 | tools | 3.00
71 | additional costs | 1.00
* 561 | project B | 45.00
etc.
How can i get the project number from the total-rows in the
corresponding individual rows i.e.
354 | 12 | housing | 2.00
354 | 32 | food | 3.00
354 | 24 | equipment | 6.00
561 | 12 | housing | 34.00
561 | 24 | equipment | 7.00
561 | 58 | tools | 3.00
561 | 71 | additional costs | 1.00
etc.
Thanks in advance,
Martin
Agh! Shoud not post while distracted.
Subquery must return just one record, of course.
Use First() or TOP 1, e.g.:
ProjectNum: ( SELECT TOP 1 [MyFirstColumn] FROM ...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u... Hi Martin.
Presumably the source table here is an import from something like a text
file. Since it depends on the order of the records in the table, it will
be important to have a primary key (e.g. a sequential AutoNumber named
ImportID) to guarantee that the records are kept in that order while you
transfer them to the real table.
1. Create a query into the temp source table - named "MyImportTable" in
this example.
2. Drag the fields you want into the grid.
3. In the Criteria row under the 2nd column (named "MyDescripField" in
this example), enter a criterion the omits the total rows. For example, if they
start with "Project ", enter:
Not Like "project *"
4. Type a subquery into a fresh column in the Field row. The subquery
needs to find the first row where the description starts with "project " and the
ImportID is greater than the ImportID on the current row. Something like
this:
ProjectNum: ( SELECT [MyFirstColumn] FROM [MyImportTable] AS Dupe
WHERE ((Dupe.[MyDescripField] Like "project *") AND (Dupe.ImportID >
[MyImportTable].ImportID))
ORDER BY ImportID )
5. After testing the query and sorting out any issues, you can turn it
into an Append query (Append on Query menu) to append the values to the real
table where you want the data.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Martin" <ma********@hotmail.com> wrote in message
news:68**************************@posting.google.c om... Dear Access-Guru's
I am far from being an access specialist and i have the following
problem.
I've got an access table with individual rows and rows with subtotals.
The thing is that the rows with the subtotals contain part of what i
want to use as the key for the individual rows. Example (rows with a *
are subtotals):
12 | housing | 2.00
32 | food | 3.00
24 | equipment | 6.00
* 354 | project A | 11.00
12 | housing | 34.00
24 | equipment | 7.00
58 | tools | 3.00
71 | additional costs | 1.00
* 561 | project B | 45.00
etc.
How can i get the project number from the total-rows in the
corresponding individual rows i.e.
354 | 12 | housing | 2.00
354 | 32 | food | 3.00
354 | 24 | equipment | 6.00
561 | 12 | housing | 34.00
561 | 24 | equipment | 7.00
561 | 58 | tools | 3.00
561 | 71 | additional costs | 1.00
etc.
Hi Allen,
Thanks for your reply. I've tried it but somehow access keep giving me
a popup to enter a value for [MyDescripField] when i run the query.
What could be wrong?
Regards,
Martin
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>... Agh! Shoud not post while distracted.
Subquery must return just one record, of course.
Use First() or TOP 1, e.g.:
ProjectNum: ( SELECT TOP 1 [MyFirstColumn] FROM ...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u... Hi Martin.
Presumably the source table here is an import from something like a text
file. Since it depends on the order of the records in the table, it will
be important to have a primary key (e.g. a sequential AutoNumber named
ImportID) to guarantee that the records are kept in that order while you
transfer them to the real table.
1. Create a query into the temp source table - named "MyImportTable" in
this example.
2. Drag the fields you want into the grid.
3. In the Criteria row under the 2nd column (named "MyDescripField" in
this example), enter a criterion the omits the total rows. For example, if they
start with "Project ", enter:
Not Like "project *"
4. Type a subquery into a fresh column in the Field row. The subquery
needs to find the first row where the description starts with "project " and the
ImportID is greater than the ImportID on the current row. Something like
this:
ProjectNum: ( SELECT [MyFirstColumn] FROM [MyImportTable] AS Dupe
WHERE ((Dupe.[MyDescripField] Like "project *") AND (Dupe.ImportID >
[MyImportTable].ImportID))
ORDER BY ImportID )
5. After testing the query and sorting out any issues, you can turn it
into an Append query (Append on Query menu) to append the values to the real
table where you want the data.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
What is the name of your field? The one that contains the description such
as "Housing", "food", or "equipment".
Repalce "MyDescripField" with the actual name of your description field.
"Martin" <ma********@hotmail.com> wrote in message
news:68**************************@posting.google.c om... Hi Allen,
Thanks for your reply. I've tried it but somehow access keep giving me
a popup to enter a value for [MyDescripField] when i run the query.
What could be wrong?
Regards,
Martin
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<3f**********************@freenews.iinet.net. au>... Agh! Shoud not post while distracted.
Subquery must return just one record, of course.
Use First() or TOP 1, e.g.:
ProjectNum: ( SELECT TOP 1 [MyFirstColumn] FROM ...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u... Hi Martin.
Presumably the source table here is an import from something like a
text file. Since it depends on the order of the records in the table, it
will be important to have a primary key (e.g. a sequential AutoNumber named
ImportID) to guarantee that the records are kept in that order while
you transfer them to the real table.
1. Create a query into the temp source table - named "MyImportTable"
in this example.
2. Drag the fields you want into the grid.
3. In the Criteria row under the 2nd column (named "MyDescripField" in
this example), enter a criterion the omits the total rows. For example, if
they start with "Project ", enter:
Not Like "project *"
4. Type a subquery into a fresh column in the Field row. The subquery
needs to find the first row where the description starts with "project " and
the ImportID is greater than the ImportID on the current row. Something
like this:
ProjectNum: ( SELECT [MyFirstColumn] FROM [MyImportTable] AS Dupe
WHERE ((Dupe.[MyDescripField] Like "project *") AND (Dupe.ImportID >
[MyImportTable].ImportID))
ORDER BY ImportID )
5. After testing the query and sorting out any issues, you can turn it
into an Append query (Append on Query menu) to append the values to the
real table where you want the data.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen,
I did replace the name in your example by the name of my description
field. Just for arguments sake i used "MyDescripField" in my reply.
Regards,
MArtin
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<3f**********************@freenews.iinet.net. au>... What is the name of your field? The one that contains the description such
as "Housing", "food", or "equipment".
Repalce "MyDescripField" with the actual name of your description field.
"Martin" <ma********@hotmail.com> wrote in message
news:68**************************@posting.google.c om... Hi Allen,
Thanks for your reply. I've tried it but somehow access keep giving me
a popup to enter a value for [MyDescripField] when i run the query.
What could be wrong?
Regards,
Martin
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<3f**********************@freenews.iinet.net. au>... Agh! Shoud not post while distracted.
Subquery must return just one record, of course.
Use First() or TOP 1, e.g.:
ProjectNum: ( SELECT TOP 1 [MyFirstColumn] FROM ...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
> Hi Martin.
>
> Presumably the source table here is an import from something like a text > file. Since it depends on the order of the records in the table, it will
be > important to have a primary key (e.g. a sequential AutoNumber named
> ImportID) to guarantee that the records are kept in that order while you > transfer them to the real table.
>
> 1. Create a query into the temp source table - named "MyImportTable" in
this > example.
>
> 2. Drag the fields you want into the grid.
>
> 3. In the Criteria row under the 2nd column (named "MyDescripField" in this > example), enter a criterion the omits the total rows. For example, if they > start with "Project ", enter:
> Not Like "project *"
>
> 4. Type a subquery into a fresh column in the Field row. The subquery needs > to find the first row where the description starts with "project " and the > ImportID is greater than the ImportID on the current row. Something like > this:
>
> ProjectNum: ( SELECT [MyFirstColumn] FROM [MyImportTable] AS Dupe
> WHERE ((Dupe.[MyDescripField] Like "project *") AND (Dupe.ImportID >
> [MyImportTable].ImportID))
> ORDER BY ImportID )
>
> 5. After testing the query and sorting out any issues, you can turn it into > an Append query (Append on Query menu) to append the values to the real > table where you want the data.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
If it is asking for "MyDescripField" as a parameter, it means that the query
cannot find this field.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Martin" <ma********@hotmail.com> wrote in message
news:68**************************@posting.google.c om... Hi Allen,
I did replace the name in your example by the name of my description
field. Just for arguments sake i used "MyDescripField" in my reply.
Regards,
MArtin
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<3f**********************@freenews.iinet.net. au>... What is the name of your field? The one that contains the description
such as "Housing", "food", or "equipment".
Repalce "MyDescripField" with the actual name of your description field.
"Martin" <ma********@hotmail.com> wrote in message
news:68**************************@posting.google.c om... Hi Allen,
Thanks for your reply. I've tried it but somehow access keep giving me
a popup to enter a value for [MyDescripField] when i run the query.
What could be wrong?
Regards,
Martin
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<3f**********************@freenews.iinet.net. au>... > Agh! Shoud not post while distracted.
>
> Subquery must return just one record, of course.
> Use First() or TOP 1, e.g.:
> ProjectNum: ( SELECT TOP 1 [MyFirstColumn] FROM ...
>
>
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:3f**********************@freenews.iinet.net.a u...
> > Hi Martin.
> >
> > Presumably the source table here is an import from something like
a text > > file. Since it depends on the order of the records in the table,
it will
be > > important to have a primary key (e.g. a sequential AutoNumber
named > > ImportID) to guarantee that the records are kept in that order
while you > > transfer them to the real table.
> >
> > 1. Create a query into the temp source table - named
"MyImportTable" in
this > > example.
> >
> > 2. Drag the fields you want into the grid.
> >
> > 3. In the Criteria row under the 2nd column (named
"MyDescripField" in this > > example), enter a criterion the omits the total rows. For example,
if they > > start with "Project ", enter:
> > Not Like "project *"
> >
> > 4. Type a subquery into a fresh column in the Field row. The
subquery needs > > to find the first row where the description starts with "project "
and the > > ImportID is greater than the ImportID on the current row.
Something like > > this:
> >
> > ProjectNum: ( SELECT [MyFirstColumn] FROM [MyImportTable] AS Dupe
> > WHERE ((Dupe.[MyDescripField] Like "project *") AND (Dupe.ImportID > [MyImportTable].ImportID))
> > ORDER BY ImportID )
> >
> > 5. After testing the query and sorting out any issues, you can
turn it into > > an Append query (Append on Query menu) to append the values to the
real > > table where you want the data.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: David Furey |
last post by:
Hi
I have an XML documnet and a XSLT document as shown below
THe XSLT document brings back a filtered docmument that has the VendorName
that starts with a particular sub-string
This works as...
|
by: eric |
last post by:
Hi,everyone:
I have my xml data defined as
xml data definition I:
<?xml version="1.0"?>
<parents>
<parent>
<id>1000</id>
|
by: GaryB |
last post by:
I have an untyped datatable that has financial numbers and controls that
were populated by code (not a simple fill from a DA). Now I want to insert
subtotals into it. I wrote a sub to do so that...
|
by: Sparky Arbuckle |
last post by:
Hello All! My problem is trying to calculate SubTotal in my FOR EACH
NEXT Loop. I am looping through depending on how many of each different
item is in the user's shopping cart. I am using each...
|
by: Dave |
last post by:
Hello
I have set up a database of students in a program, mainly for
statistics. The report I'm having trouble with right now is one that
graphs admission rates on a per-term basis.
Here's...
|
by: Sam Durai |
last post by:
A join query is running against my db which is logically partitioned.
The join involves a partitioned table (has around 300 Million rows)
which is spread across 7 partitions and a small...
|
by: xian2 |
last post by:
Hi All,
I am trying to create a subtotal text box much like the one in the orders form of the northwind database where you have a subtotal text box in the footer of the subform and then have that...
|
by: tam76131 |
last post by:
Hello,
I'm very new to access and have been struggling with the subtotal function in access.
I have a very large table with the following info
State City ID Month ...
|
by: eskelies |
last post by:
Hello all,
Does anyone have any code that will subtotal a query in Access?
Thank you.
BEFORE:
Account Code Principal Income
4 BUY $10.00 $0.00
4 BUY $10.00 $0.00
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
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...
|
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...
|
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,...
| |
