Append Query help 1

[Tazcat]

I have an append query that I use to create an archive table. The SQL is below:

INSERT INTO tbl_RecallArchive ( [Report Date], [Item Type], [Age Category], [#Items], [Current Value] )
SELECT Date() AS [Report Date], smry_RecallsAging.[Item Type], smry_RecallsAging.[Age Category], smry_RecallsAging.[#Items], smry_RecallsAging.[Current Value]
FROM smry_RecallsAging;

The query works fine. It pulls data from a summary query based on the primary record table. The part I need help with is this. The key value they need to be able to track is the [Age Category]. This field generally has values of: 1-5 days, 6-10 days, 11-15 days, and 16+ days. However, the primary table may, at any time, be missing records for any one of these category values.

What they want is, where the category value has no records, to insert into the Archive table a row where the [#Items] and [Current Value] fields are null (but the [Report Date], [Item Type] and [Age Category] fields are populated).

Is this something I can force/trick either the Summary Query or the Append Query into doing for me, or am I better off writing this into the macro that runs the stored procedure?

 

[Golom]

The big problem is that, given that the values are missing from this table, you would need to have them available elsewhere in order to add them. Let's suppose that all the codes exist in some table that we'll call
[tt]
tblDayRange
Range
1-5
6-10
11-15
16+
[/tt]
Then

INSERT INTO tbl_RecallArchive ( [Report Date], [Item Type], [Age Category], [#Items], [Current Value] )

Select * From

(SELECT Date() AS [Report Date], smry_RecallsAging.[Item Type], smry_RecallsAging.[Age Category], smry_RecallsAging.[#Items], smry_RecallsAging.[Current Value]
FROM smry_RecallsAging

UNION ALL

Select Date(), NULL, Range, NULL, NULL
From Ranges R
Where
   NOT EXISTS (Select 1 From smry_RecallsAging X
               Where X.[Age Category] = R.Range)
)

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Tazcat]

Nice idea, thanks! I'll give it a try in the morning and let you know how it works.

Melanie

 

[Tazcat]

Golom,
can't thank you enough for the help! Worked like a charm. I had to add the additional columns to the table with blank values, but it still worked perfectly!

Melanie

 

[Golom]

I don't know what you mean by "additional columns" exactly but if you want fields other than [Age Category] populated then replace the NULLs with the default value that you want to use. For example

Select Date(), [COLOR=red]'X'[/color], Range, NULL, NULL

would default [Item Type] to 'X'.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Tazcat]

Hi Golom,
I structure the query according to your suggestions, and it has been working great. Now however, I need to change how I am doing this, and run it not from a stored procedure, but from VBA. When I created the stored procedure, the SQL looked like this:

INSERT INTO tbl_RecallArchive ( [Report Date], [Item Type], [Age Category], [#Items], [Current Value] )

Select * From

(SELECT Date() AS [Report Date], stbl_tmpArchv.StockBond, tbl_tmpArchv.[Age Category], tbl_tmpArchv.[#Items], tbl_tmpArchv.[Current Shares], tbl_tmpArchv.[Current Value]
FROM tbl_tmpArchv

UNION ALL

Select Date(), Type, Category, '0','0','0'
From tbl_AgeCat R
Where
   NOT EXISTS (Select 1 From From tbl_tmpArchv X
               Where X.[Age Category] = R.Category AND X.StockBond = R.Type)
)

Access, through it's infinet wisdom, understood that, but converted it to the following:

INSERT INTO tbl_RecallArchive
SELECT *
FROM [SELECT Date() AS [Report Date], tbl_tmpArchv.StockBond, tbl_tmpArchv.[Age Category], tbl_tmpArchv.[#Items], tbl_tmpArchv.[Current Shares], tbl_tmpArchv.[Current Value]
FROM tbl_tmpArchv

UNION ALL

Select Date(), Type, Category, '0','0','0'
From tbl_AgeCat  R
Where
   NOT EXISTS (Select 1 From  tbl_tmpArchv X
               Where X.[Age Category] = R.Category AND X.StockBond = R.Type)
]. AS [%$##@_Alias];

That was fine, and it still worked, but any time I needed to change something, I had to re-write the code in the original format, as Access couldn't understand it's own conversion!

The problem is that I need run this from VBA now, so that I can pass in a variable Report Date value. Before, it used the current date to create the Archive. Now, it needs to use the last Business date (either yesterday [Date-1] or in the case of a Monday, the prior Friday [Date-3]).

I'm banging my head against a wall here now, because I have tried structuring this both as originally written, and as Access has convered it, and NEITHER string will work. In my version, I get an error in the INSERT INTO statement. In Access' version, I get an error in the FROM statement.

Any ideas how I can structure/restructure this to work from VBA? This below throws the INSERT INTO error:

comNew.CommandText = "INSERT INTO tbl_RecallArchive" & _
         "SELECT * FROM " & _
         "(SELECT #" & dtUpDtDate & "# AS [Report Date], tbl_tmpArchv.StockBond, tbl_tmpArchv.[Age Category], " & _
         "tbl_tmpArchv.[#Items], tbl_tmpArchv.[Current Shares], tbl_tmpArchv.[Current Value]" & _
         "FROM tbl_tmpArchv" & _
         "UNION ALL" & _
         "Select #" & dtUpDtDate & "#, Type, Category, '0','0','0'" & _
         "From tbl_AgeCat  R" & _
         "Where" & _
         "NOT EXISTS (Select 1 From  tbl_tmpArchv X" & _
         "Where X.[Age Category] = R.Category AND X.StockBond = R.Type)" & _
         ");"

comNew.CommandType = adCmdText
comNew.Execute

Thanks!
Melanie

 

[Golom]

If that's exactly the way you typed it then note that you're missing some spaces.

comNew.CommandText = "INSERT INTO tbl_RecallArchive[COLOR=red yellow] [/color]" & _
         "SELECT * FROM " & _
         "(SELECT #" & dtUpDtDate & "# AS [Report Date], tbl_tmpArchv.StockBond, tbl_tmpArchv.[Age Category], " & _
         "tbl_tmpArchv.[#Items], tbl_tmpArchv.[Current Shares], tbl_tmpArchv.[Current Value][COLOR=red yellow] [/color]" & _
         "FROM tbl_tmpArchv[COLOR=red yellow] [/color]" & _
         "UNION ALL[COLOR=red yellow] [/color]" & _
         "Select #" & dtUpDtDate & "#, Type, Category, '0','0','0'[COLOR=red yellow] [/color]" & _
         "From tbl_AgeCat  R[COLOR=red yellow] [/color]" & _
         "Where[COLOR=red yellow] [/color]" & _
         "NOT EXISTS (Select 1 From  tbl_tmpArchv X[COLOR=red yellow] [/color]" & _
         "Where X.[Age Category] = R.Category AND X.StockBond = R.Type)" & _
         ");"

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Tazcat]

BLESS YOU!

You saved my bacon again. I can't believe I missed something that obvious... Stars for you!

Melanie