Solved

add field to table if it doesn't exist

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now