Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

add field to table if it doesn't exist

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
NO_CARRIER
Asked:
NO_CARRIER
  • 3
  • 2
1 Solution
 
UniqueDataCommented:
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
 
UniqueDataCommented:
The last part, 'In', will make a column appear even if there are no sales in, say, June
0
 
pteranodon72Commented:
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
 
UniqueDataCommented:
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
 
pteranodon72Commented:
UniqueData -- I didn't know the SQL syntax method.  Live and learn! pT
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now