Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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