Fill-in text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there, below is my query that I get from a tablle:

Row 1 AZX
Row 2
Row 3
Row 4

Row 2, 3, and 4 are blank and I would like to fill row 2, 3, and 4 with
"AZX". How can I do that? Thanks in advance for any help that you can give me.
 
Print the query, pick up your pencil, and write "AZX" into the appropriate
spaces ;-)

More seriously, you cannot expect to get a reply to this question , without
supplying relevant details. At a bare minimum, the SQL of the query might
do - but more might be needed. And what you posted is not "my query that I
get from a tablle"; it is the output of/result returned by your query. We
are not psychic - we cannot see your query.

Rob
 
I assume that you know what's a query is, right? It's when you click new,
design view, and you select a table to create your query. Whatever field that
you select, it will show in your query. That is exactly what I did: SELECT
Table1.Name, Table1.Date, Table1.[Fee Paid]
FROM Table1

In the table there are 3 fields. One is name that where the "AXZ" is from,
and the others are Date and Fee paid. For example,

Name Date Fee paid
AXZ 4/1/07 $10
4/15/07 $15
4/20/07 $10
5/1/07 $5
BGT 4/12/07 $25
4/25/07 $8

So there are 3 blank row under Name that I want to fill with "AXZ".
 
I certainly do know what a query is :-)

Your sample data indicates that you don't really understand what an Access
database, and its tables, is really all about. This looks like data
imported from a spreadsheet, with no data (null values) in some fields. In
a spreadsheet, you are assuming that if the value in a field in a record
(row) is missing, it is the same as the previous value in that field
(column). Sadly, it doesn't work like that in a table in an Access
database. The records in a table have no implicit order, and will be
returned in the default order of the fields in the table (if no other order
is specified).

Your posted query:
"SELECT Table1.Name, Table1.Date, Table1.[Fee Paid] FROM Table1"
will not return the result set you posted UNLESS you have an autonumber
field in your table, and you are relying on that to order your records. If
you are doing that, then you are setting yourself up for potential problems,
because autonumbers are NOT guaranteed to be sequentially increasing.

BTW, two of your field names ("Name" and "Date") are reserved words in
Access; using them as field names may cause problems within your database.

As to your original question, the answer is that you can design an update
query (you know what an update query is, right?) to replace the null values
with the last non-null value from a record with a lower autonumber. I'll
let you sort out the trivial details of how to do it ;-)

HTH,

Rob


Bltony said:
I assume that you know what's a query is, right? It's when you click new,
design view, and you select a table to create your query. Whatever field
that
you select, it will show in your query. That is exactly what I did: SELECT
Table1.Name, Table1.Date, Table1.[Fee Paid]
FROM Table1

In the table there are 3 fields. One is name that where the "AXZ" is from,
and the others are Date and Fee paid. For example,

Name Date Fee paid
AXZ 4/1/07 $10
4/15/07 $15
4/20/07 $10
5/1/07 $5
BGT 4/12/07 $25
4/25/07 $8

So there are 3 blank row under Name that I want to fill with "AXZ".

Rob Parker said:
Print the query, pick up your pencil, and write "AZX" into the
appropriate
spaces ;-)

More seriously, you cannot expect to get a reply to this question ,
without
supplying relevant details. At a bare minimum, the SQL of the query
might
do - but more might be needed. And what you posted is not "my query that
I
get from a tablle"; it is the output of/result returned by your query.
We
are not psychic - we cannot see your query.

Rob
Click to expand...
Click to expand...
 
If the table with the autonumber is called t_au, if the autonumber field is
the primary key and is called au, if f1 is the field name that contains
sometimes data, sometimes null (to be replace by data) then


UPDATE t_au INNER JOIN t_au AS t_au_1
ON t_au.au=t_au_1.au+1
SET t_au.f1 = t_au_1.f1
WHERE t_au.f1 IS NULL ;


should do the job, with Jet.


Hoping it may help,
Vanderghast, Access MVP


Rob Parker said:
I certainly do know what a query is :-)

Your sample data indicates that you don't really understand what an Access
database, and its tables, is really all about. This looks like data
imported from a spreadsheet, with no data (null values) in some fields.
In a spreadsheet, you are assuming that if the value in a field in a
record (row) is missing, it is the same as the previous value in that
field (column). Sadly, it doesn't work like that in a table in an Access
database. The records in a table have no implicit order, and will be
returned in the default order of the fields in the table (if no other
order is specified).

Your posted query:
"SELECT Table1.Name, Table1.Date, Table1.[Fee Paid] FROM Table1"
will not return the result set you posted UNLESS you have an autonumber
field in your table, and you are relying on that to order your records.
If you are doing that, then you are setting yourself up for potential
problems, because autonumbers are NOT guaranteed to be sequentially
increasing.

BTW, two of your field names ("Name" and "Date") are reserved words in
Access; using them as field names may cause problems within your database.

As to your original question, the answer is that you can design an update
query (you know what an update query is, right?) to replace the null
values with the last non-null value from a record with a lower autonumber.
I'll let you sort out the trivial details of how to do it ;-)

HTH,

Rob


Bltony said:
I assume that you know what's a query is, right? It's when you click new,
design view, and you select a table to create your query. Whatever field
that
you select, it will show in your query. That is exactly what I did:
SELECT
Table1.Name, Table1.Date, Table1.[Fee Paid]
FROM Table1

In the table there are 3 fields. One is name that where the "AXZ" is
from,
and the others are Date and Fee paid. For example,

Name Date Fee paid
AXZ 4/1/07 $10
4/15/07 $15
4/20/07 $10
5/1/07 $5
BGT 4/12/07 $25
4/25/07 $8

So there are 3 blank row under Name that I want to fill with "AXZ".

Rob Parker said:
Print the query, pick up your pencil, and write "AZX" into the
appropriate
spaces ;-)

More seriously, you cannot expect to get a reply to this question ,
without
supplying relevant details. At a bare minimum, the SQL of the query
might
do - but more might be needed. And what you posted is not "my query
that I
get from a tablle"; it is the output of/result returned by your query.
We
are not psychic - we cannot see your query.

Rob


Hi there, below is my query that I get from a tablle:

Row 1 AZX
Row 2
Row 3
Row 4

Row 2, 3, and 4 are blank and I would like to fill row 2, 3, and 4
with
"AZX". How can I do that? Thanks in advance for any help that you can
give
me.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top