Null Values in a Make Table Qry

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

I've spent the last 3 hours trying to figure out how to handle Null values
when using a Make Table Query that calls other queries. This entire project
has been a nightmare!

Here's my dillemma:
My original data is imported into various Access tables nightly from a
Mid-Frame. That's the easy part. The portion of the data that I am mainly
interest in is kinda the "Month to date". Simply put, we are into March,
thus there would be data that reads as follows:
# of x Date
300 200401
33 200402
56 200403

and so on. I want my create a new to that has fields for every month even
though there may not be any data and there won't be for the remaining 9
months. My problem is that the Make Rable Query won't work is the values are
Null. Can somone please help?

Thanks,
Tony
(e-mail address removed)
 
Anthony,

Not knowing how you're trying to do it, I can't offer anything concrete, but
you might want to check the online help for the Nz() function.

Why do you want a separate column for every month? It would make more sense
(to me) to simply put them into two columns (No, Date).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
This was an existing DB and I'm trying to minimize my work. I do want to
create a seperated column for each month, but when there aren't any values
the Mktbl qry won't create any rows. I've also tried the Nz() function, but
I can't get it to populate the Null field with a 0.
 
Anthony,

Can you show me the query you're using now?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
My original data is imported into various Access tables nightly from a
Mid-Frame. That's the easy part. The portion of the data that I am mainly
interest in is kinda the "Month to date". Simply put, we are into March,
thus there would be data that reads as follows:
# of x Date
300 200401
33 200402
56 200403

and so on. I want my create a new to that has fields for every month even
though there may not be any data and there won't be for the remaining 9
months. My problem is that the Make Rable Query won't work is the values are
Null. Can somone please help?
Click to expand...

Whoa. Why would you want a Table with "fields for every month" AT ALL?
That's a spreadsheet, not a table; storing data in fieldnames is
neither necessary nor appropriate.

If you *must* do this, just import the data as you have shown it and
create a Crosstab query, using the month (extracted from your non-date
field [Date]) as the Column Header; if you must have a non-normalized
table with fields 01, 02, 03, ... , 12 then run an Append query based
on the Crosstab query.
 
Here is the SQL view of the Cross Tab Query. Keep in mind that there is only
going to be 3 months of data (Jan, Feb, Mar). Yet when I create a table
using a make table query, I want to be able to fill in the reamining months
with a 0.

TRANSFORM Sum([GSV Actuals].[Units Sold]) AS [SumOfUnits Sold]
SELECT [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip
FROM (([Ship To Customer] INNER JOIN [Purchasing Groups] ON [Ship To
Customer].PurchGroup = [Purchasing Groups].PurchGroup) INNER JOIN [GSV
Actuals] ON [Ship To Customer].[Ship To] = [GSV Actuals].[Ship To]) INNER
JOIN [Product Master] ON [GSV Actuals].[Prod No] = [Product Master].[Prod
No]
WHERE ((([GSV Actuals].Period) Like "2004*") AND (([Purchasing
Groups].[Group Name])="CCE"))
GROUP BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip, [Purchasing Groups].[Group
Name]
ORDER BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Ship To
Customer].[Add 4]
PIVOT [GSV Actuals].Period;
 
Anthony,

I'm afraid I can't think of how to do it with a transform query. Perhaps
someone with more SQL expertise than me can offer something constructive.

The only way I can suggest is to have the table already built, with each
field's Default Value = 0.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Anthony Viscomi said:
Here is the SQL view of the Cross Tab Query. Keep in mind that there is only
going to be 3 months of data (Jan, Feb, Mar). Yet when I create a table
using a make table query, I want to be able to fill in the reamining months
with a 0.

TRANSFORM Sum([GSV Actuals].[Units Sold]) AS [SumOfUnits Sold]
SELECT [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip
FROM (([Ship To Customer] INNER JOIN [Purchasing Groups] ON [Ship To
Customer].PurchGroup = [Purchasing Groups].PurchGroup) INNER JOIN [GSV
Actuals] ON [Ship To Customer].[Ship To] = [GSV Actuals].[Ship To]) INNER
JOIN [Product Master] ON [GSV Actuals].[Prod No] = [Product Master].[Prod
No]
WHERE ((([GSV Actuals].Period) Like "2004*") AND (([Purchasing
Groups].[Group Name])="CCE"))
GROUP BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip, [Purchasing Groups].[Group
Name]
ORDER BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Ship To
Customer].[Add 4]
PIVOT [GSV Actuals].Period;

Graham R Seach said:
Anthony,

Can you show me the query you're using now?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

function,
but
Click to expand...
from
a remaining
Click to expand...
Click to expand...
 
Thanks Graham!
I found somewhat of a "work around", though it requires much more effort.
Graham R Seach said:
Anthony,

I'm afraid I can't think of how to do it with a transform query. Perhaps
someone with more SQL expertise than me can offer something constructive.

The only way I can suggest is to have the table already built, with each
field's Default Value = 0.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Anthony Viscomi said:
Here is the SQL view of the Cross Tab Query. Keep in mind that there is only
going to be 3 months of data (Jan, Feb, Mar). Yet when I create a table
using a make table query, I want to be able to fill in the reamining months
with a 0.

TRANSFORM Sum([GSV Actuals].[Units Sold]) AS [SumOfUnits Sold]
SELECT [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip
FROM (([Ship To Customer] INNER JOIN [Purchasing Groups] ON [Ship To
Customer].PurchGroup = [Purchasing Groups].PurchGroup) INNER JOIN [GSV
Actuals] ON [Ship To Customer].[Ship To] = [GSV Actuals].[Ship To]) INNER
JOIN [Product Master] ON [GSV Actuals].[Prod No] = [Product Master].[Prod
No]
WHERE ((([GSV Actuals].Period) Like "2004*") AND (([Purchasing
Groups].[Group Name])="CCE"))
GROUP BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip, [Purchasing Groups].[Group
Name]
ORDER BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Ship To
Customer].[Add 4]
PIVOT [GSV Actuals].Period;

Graham R Seach said:
Anthony,

Can you show me the query you're using now?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

This was an existing DB and I'm trying to minimize my work. I do
Click to expand...
Click to expand...
want
to from
Click to expand...
Click to expand...
 
Anthony,

Care to elaborate?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Anthony Viscomi said:
Thanks Graham!
I found somewhat of a "work around", though it requires much more effort.
Graham R Seach said:
Anthony,

I'm afraid I can't think of how to do it with a transform query. Perhaps
someone with more SQL expertise than me can offer something constructive.

The only way I can suggest is to have the table already built, with each
field's Default Value = 0.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Anthony Viscomi said:
Here is the SQL view of the Cross Tab Query. Keep in mind that there
Click to expand...
is
only
going to be 3 months of data (Jan, Feb, Mar). Yet when I create a table
using a make table query, I want to be able to fill in the reamining months
with a 0.

TRANSFORM Sum([GSV Actuals].[Units Sold]) AS [SumOfUnits Sold]
SELECT [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip
FROM (([Ship To Customer] INNER JOIN [Purchasing Groups] ON [Ship To
Customer].PurchGroup = [Purchasing Groups].PurchGroup) INNER JOIN [GSV
Actuals] ON [Ship To Customer].[Ship To] = [GSV Actuals].[Ship To]) INNER
JOIN [Product Master] ON [GSV Actuals].[Prod No] = [Product Master].[Prod
No]
WHERE ((([GSV Actuals].Period) Like "2004*") AND (([Purchasing
Groups].[Group Name])="CCE"))
GROUP BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip, [Purchasing Groups].[Group
Name]
ORDER BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Ship To
Customer].[Add 4]
PIVOT [GSV Actuals].Period;

Anthony,

Can you show me the query you're using now?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

This was an existing DB and I'm trying to minimize my work. I do
Click to expand...
want
to
create a seperated column for each month, but when there aren't any
values
the Mktbl qry won't create any rows. I've also tried the Nz() function,
but
I can't get it to populate the Null field with a 0.
Anthony,

Not knowing how you're trying to do it, I can't offer anything
concrete,
but
you might want to check the online help for the Nz() function.

Why do you want a separate column for every month? It would make more
sense
(to me) to simply put them into two columns (No, Date).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I've spent the last 3 hours trying to figure out how to handle Null
values
when using a Make Table Query that calls other queries. This entire
project
has been a nightmare!

Here's my dillemma:
My original data is imported into various Access tables
Click to expand...
nightly
from
a
Mid-Frame. That's the easy part. The portion of the data that
Click to expand...
Click to expand...
I
am
Click to expand...
Click to expand...
 
The client that I am working for is a supplier of a part for the Coca-Cola
vending machines. Coke has asked my client to fill in the # of parts/units
supplied for each month, by Division with a XLS template they provided, thus
the multiple worksheets within the XLS. My client captures the data via a
mid-frame and re-creates a MDB nightly with the 4 tables that contain the
pertinant data. I was contracted to automate the population of the XLS,
which prior was done manually.
My "work around"is:
Create multiple named ranges (because not all of the cells are adjoining,
thus I can't create a continous link via Access)
Create a "boat load" of XLS table links to Access and just as many Update
Queries to those tables

Lotta work, huh?
Graham R Seach said:
Anthony,

Care to elaborate?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Anthony Viscomi said:
Thanks Graham!
I found somewhat of a "work around", though it requires much more effort.
Graham R Seach said:
Anthony,

I'm afraid I can't think of how to do it with a transform query. Perhaps
someone with more SQL expertise than me can offer something constructive.

The only way I can suggest is to have the table already built, with each
field's Default Value = 0.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Here is the SQL view of the Cross Tab Query. Keep in mind that there is
only
going to be 3 months of data (Jan, Feb, Mar). Yet when I create a table
using a make table query, I want to be able to fill in the reamining
months
with a 0.

TRANSFORM Sum([GSV Actuals].[Units Sold]) AS [SumOfUnits Sold]
SELECT [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip
FROM (([Ship To Customer] INNER JOIN [Purchasing Groups] ON [Ship To
Customer].PurchGroup = [Purchasing Groups].PurchGroup) INNER JOIN [GSV
Actuals] ON [Ship To Customer].[Ship To] = [GSV Actuals].[Ship To]) INNER
JOIN [Product Master] ON [GSV Actuals].[Prod No] = [Product Master].[Prod
No]
WHERE ((([GSV Actuals].Period) Like "2004*") AND (([Purchasing
Groups].[Group Name])="CCE"))
GROUP BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip, [Purchasing Groups].[Group
Name]
ORDER BY [GSV Actuals].[Ship Date], [Ship To Customer].State, [Ship To
Customer].[Add 4]
PIVOT [GSV Actuals].Period;

Anthony,

Can you show me the query you're using now?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

This was an existing DB and I'm trying to minimize my work. I do want
to
create a seperated column for each month, but when there aren't any
values
the Mktbl qry won't create any rows. I've also tried the Nz()
function,
but
I can't get it to populate the Null field with a 0.
Anthony,

Not knowing how you're trying to do it, I can't offer anything
concrete,
but
you might want to check the online help for the Nz() function.

Why do you want a separate column for every month? It would make
more
sense
(to me) to simply put them into two columns (No, Date).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I've spent the last 3 hours trying to figure out how to handle
Null
values
when using a Make Table Query that calls other queries. This
entire
project
has been a nightmare!

Here's my dillemma:
My original data is imported into various Access tables nightly
from
a
Mid-Frame. That's the easy part. The portion of the data
Click to expand...
Click to expand...
that
I is
the
Click to expand...
Click to expand...
 
Anthony,

YUK! I wish I knew more about Queries to help. Perhaps one of the other MVPs
would jump in here?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Anthony Viscomi said:
The client that I am working for is a supplier of a part for the Coca-Cola
vending machines. Coke has asked my client to fill in the # of parts/units
supplied for each month, by Division with a XLS template they provided, thus
the multiple worksheets within the XLS. My client captures the data via a
mid-frame and re-creates a MDB nightly with the 4 tables that contain the
pertinant data. I was contracted to automate the population of the XLS,
which prior was done manually.
My "work around"is:
Create multiple named ranges (because not all of the cells are adjoining,
thus I can't create a continous link via Access)
Create a "boat load" of XLS table links to Access and just as many Update
Queries to those tables

Lotta work, huh?
Graham R Seach said:
Anthony,

Care to elaborate?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Anthony Viscomi said:
Thanks Graham!
I found somewhat of a "work around", though it requires much more effort.
Anthony,

I'm afraid I can't think of how to do it with a transform query. Perhaps
someone with more SQL expertise than me can offer something constructive.

The only way I can suggest is to have the table already built, with each
field's Default Value = 0.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Here is the SQL view of the Cross Tab Query. Keep in mind that
Click to expand...
there
is
only
going to be 3 months of data (Jan, Feb, Mar). Yet when I create a table
using a make table query, I want to be able to fill in the reamining
months
with a 0.

TRANSFORM Sum([GSV Actuals].[Units Sold]) AS [SumOfUnits Sold]
SELECT [GSV Actuals].[Ship Date], [Ship To Customer].State, [Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip
FROM (([Ship To Customer] INNER JOIN [Purchasing Groups] ON [Ship To
Customer].PurchGroup = [Purchasing Groups].PurchGroup) INNER JOIN [GSV
Actuals] ON [Ship To Customer].[Ship To] = [GSV Actuals].[Ship To])
INNER
JOIN [Product Master] ON [GSV Actuals].[Prod No] = [Product
Master].[Prod
No]
WHERE ((([GSV Actuals].Period) Like "2004*") AND (([Purchasing
Groups].[Group Name])="CCE"))
GROUP BY [GSV Actuals].[Ship Date], [Ship To Customer].State,
[Purchasing
Groups].Company, [Ship To Customer].[Add 4], [GSV Actuals].[Prod No],
[Product Master].Desc, [Ship To Customer].Zip, [Purchasing
Groups].[Group
Name]
ORDER BY [GSV Actuals].[Ship Date], [Ship To Customer].State,
Click to expand...
Click to expand...
[Ship
To
Customer].[Add 4]
PIVOT [GSV Actuals].Period;

Anthony,

Can you show me the query you're using now?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

This was an existing DB and I'm trying to minimize my work. I do
want
to
create a seperated column for each month, but when there
Click to expand...
aren't
any
values
the Mktbl qry won't create any rows. I've also tried the Nz()
function,
but
I can't get it to populate the Null field with a 0.
Anthony,

Not knowing how you're trying to do it, I can't offer anything
concrete,
but
you might want to check the online help for the Nz() function.

Why do you want a separate column for every month? It would make
more
sense
(to me) to simply put them into two columns (No, Date).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I've spent the last 3 hours trying to figure out how to handle
Null
values
when using a Make Table Query that calls other queries. This
entire
project
has been a nightmare!

Here's my dillemma:
My original data is imported into various Access tables nightly
from
a
Mid-Frame. That's the easy part. The portion of the data
Click to expand...
that
I
am
mainly
interest in is kinda the "Month to date". Simply put, we are
into
March,
thus there would be data that reads as follows:
# of x Date
300 200401
33 200402
56 200403

and so on. I want my create a new to that has fields for every
month
even
though there may not be any data and there won't be for the
remaining
9
months. My problem is that the Make Rable Query won't work is
the
values
are
Null. Can somone please help?

Thanks,
Tony
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top