Export only fields that are populated in a query

[access_banker]

I have a table that has several fields, not all of which are used for
each record. I am looking to export a query on the table, but I only
want to export if the field is populated. Another words one record may
have information in Field1 and Field2, but another record has
information in those fields plus Field3 and Field4. The entire table
has 10 fields, but I do not want to export them. How do I export a
field only if there is data in it?

Thanks.
Brian

 

[jlepack]

When you create your Query in the criteria box type not "" (two
quotation marks). This will only select that data that has something
in that field.

 

[Brendan Reynolds]

What do you want to export it to? Another database? A spreadsheet? A text
file? Something else?

Another database is likely to require the same number of columns in each
row. A text file may not impose an such restriction, but might potentially
cause difficulties further down the line for any other app - or potentially
for a human reader - that needed to make sense of that text file.

XML might be an option. But as indicated above, a lot depends on what you
need to do with that data after you export it.

 

[access_banker]

When you create your Query in the criteria box type not "" (two
quotation marks). This will only select that data that has something
in that field.

Thanks for the reply, but that will populate my query with data that is
populated, but when I go to export it will still send all the fields in
the table or query, as I have to have them in there seeing they may
have info. I only want to export out the fields that have values in
them. So from my example above I would export only Field1, Field2,
Field3, and Field4. The rest of the fields would not make the exported
file.

Thanks.
Brian

 

[John Vinson]

I have a table that has several fields, not all of which are used for
each record. I am looking to export a query on the table, but I only
want to export if the field is populated. Another words one record may
have information in Field1 and Field2, but another record has
information in those fields plus Field3 and Field4. The entire table
has 10 fields, but I do not want to export them. How do I export a
field only if there is data in it?

Thanks.
Brian

Could you give an example of your data and the desired export
appearance? I am worried that your table structure may not be properly
normalized; if you have ten fields all containing "the same kind" of
value, i.e. a one to many relationship embedded in the same record,
you may be in trouble.

John W. Vinson[MVP]

 

[access_banker]

The output is a text file that would be used to upload into a mainframe
application.

What this is a rules engine to track rules that are used to set up how
a record from a source system is to be managed and what information is
needed to be added for the financial system to be able to process it.

The main table houses the rules and it structure is as follows:
ID no - autonumber
SYSIS - identifies the system the rule applies to
Subgroup# - this is used to identify the specific subgroup the rule
applies to, each subgroup has a specific task in the process
key1 - these house a key to be used to determine and action
key2
key3
key4
key5
..
..
Key15
Ans1 - this is the defined action to be taken for a record that matches
the keys
Ans2
Ans3
..
..
Ans15

All the fields are text formatted.

I need to export the information, but I have been asked to only provide
the export to have fields with information in them. It would be broken
down by SYS ID and Subgroup #. The think is that not every table will
use all the keys or ans, so the export should only reflect those that
have values.

Example would be for Subgroup#1 the following keys and ans would be
used:

Key 1
Key 2
Ans1

But for Subgroup#2 the following would be used:

Key1
Ans1
Ans2

The other problems is that this may change from SYS ID to SYS ID. I do
not want to have separate export programs for each sub group and each
SYS ID, I want to run one export that will just ignore any field that
does not have values in it.

when I try to do it with query and make a table, I need to look at each
field to determine if there are values and then it puts it into the
table if it is on the query. I have not been able to figure out how to
do this without including all the fields of Rules table in the export.

Any ideas greatly appreciated.

Brian

 

[John Spencer]

What is the output file's structure? Is it a comma-delimited text file? Or
is it more like an XML structure? OR ...

With Comma delimited text it might be feasible to use a calculated field
that concatenates the non-null values with the appropriate separator.
Otherwise, you might be looking at writing a bit of VBA code for the export.
IF the keyfields are numeric, something like the following might work to
give you an exportable query set.

SELECT IDNo
, SYSIS
, Mid( ("," + KeyFieldA ) & ("," + KeyFieldB )& ("," + KeyFieldC ) , 2) as
Keys
FROM YourTable