Code for creating field when no value exists

  • Thread starter Thread starter Jodie
  • Start date Start date
J

Jodie

I have files that I need to import into a data base. All of the files have
the same format and layout. They all have rows that reflect sources per
person; however the number of sources (rows) me vary from file to file. I am
generating a final pivot table to export, but I need the table to include all
possible sources even if there is no value. Does anyone know how I can do
that?
 
If you know the names of the columns, you can do this.

== Open the crosstab in design view
== Select View: Properties from the menu
== In the query's column headings property type the names of the columns
(sources) you want to appear - separated by commas
aardvark, abacus, client, ...
== When you exit from that property, Access will automatically add the quote
marks around each item.

You can also edit this in the SQL view. Change the IN clause to add the
column headings.

Transform ...
Select ...
FROM ...
GROUP BY ...
PIVOT [Sources] IN ("aardvark", "abacus", "client")

One thing you need to know is that all the columns you designate will show up,
but any additional columns that might be there will NOT show up - only the
columns you specify will appear.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
On Wed, 23 Dec 2009 12:38:01 -0800, Jodie

Create the pivot not over the table, but over a query that outer joins
the table with another table listing all possible sources.

-Tom.
Microsoft Access MVP
 
Thank you Tom. This is what I was trying but it still wasn't giving me all
of the sources.
 
Thanks John, I got it to work with some tweaking. I used your information an
combined it with a code that I wrote for something else and now it works. I
appreciate your help.
--
Thank you, Jodie


John Spencer said:
If you know the names of the columns, you can do this.

== Open the crosstab in design view
== Select View: Properties from the menu
== In the query's column headings property type the names of the columns
(sources) you want to appear - separated by commas
aardvark, abacus, client, ...
== When you exit from that property, Access will automatically add the quote
marks around each item.

You can also edit this in the SQL view. Change the IN clause to add the
column headings.

Transform ...
Select ...
FROM ...
GROUP BY ...
PIVOT [Sources] IN ("aardvark", "abacus", "client")

One thing you need to know is that all the columns you designate will show up,
but any additional columns that might be there will NOT show up - only the
columns you specify will appear.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have files that I need to import into a data base. All of the files have
the same format and layout. They all have rows that reflect sources per
person; however the number of sources (rows) me vary from file to file. I am
generating a final pivot table to export, but I need the table to include all
possible sources even if there is no value. Does anyone know how I can do
that?
Click to expand...
.
Click to expand...
 
Back
Top