Solved

add field to table if it doesn't exist

Posted on 2008-10-31
5
205 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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

685 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