Solved

add field to table if it doesn't exist

Posted on 2008-10-31
5
203 Views
Last Modified: 2012-05-05
I am making a table based on a Crosstab query.   The possible fields are 1, 2, 3, 4.
If the crosstab query does not come across any 4's then the resulting table will only show 1, 2, 3.

The problem is, the subsequent queries based on the crosstab query looks for a value for 1, 2, 3, 4.  (even if blank).

So after the cross tab query makes the table, I need to add any 'empty' fields to the table if it dosn't already exist.
0
Comment
Question by:NO_CARRIER
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:UniqueData
ID: 22853701
TRANSFORM Sum([M_Sales].[Amount]) AS SumOfAmount
SELECT [M_Sales].[Department]
FROM M_Sales
GROUP BY [M_Sales].[Department]
PIVOT Format([M_Sales].[Sale_date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

0
 
LVL 7

Expert Comment

by:UniqueData
ID: 22853706
The last part, 'In', will make a column appear even if there are no sales in, say, June
0
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 50 total points
ID: 22853873
In the query design page of your crosstab query, View->Properties for the query.  There will be a field for Column Headings. Type in the column names (in the order you want them).  That way, [only] they will be included in the crosstab query even if no data match.  If your Column Heading field is numeric, use 1,2,3,4
If the column heading field is text, use "1", "2", "3", "4".

HTH,

pTeranodon
0
 
LVL 7

Expert Comment

by:UniqueData
ID: 22854043
I never knew you could view it in the properies like that and it would create the In Statemnt.  I always switched to the SQL view and manually typed it.

Cool.  More than two ways to skin a cat eh?...no offense cat lovers :)
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 22860180
UniqueData -- I didn't know the SQL syntax method.  Live and learn! pT
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question