Append Query Null issue

[johnwiseman]

Hi.

I have a Make Table query taking information from one table and making a new table based on the results. This works fine if there are results to make the table from, but if the query returnes no results then I get the message "You are about to Paste "0" records into a new table". Whilst the table structure is there, it has no results. What I need is for this table to create a record and record there is no result. A "0" or anything.

Here is the SQL...

SELECT [tbl Main Input].[Error Code E], Count([tbl Main Input].ID) AS CountOfID INTO [tbl Errors E]
FROM [tbl Main Input]
WHERE ((([tbl Main Input].Date) Between [forms]![frm mi]![startdate] And [forms]![frm mi]![enddate]))
GROUP BY [tbl Main Input].[Error Code E]
HAVING ((([tbl Main Input].[Error Code E])<>0));

 

[dhookom]

I would just run the above query. Then use code to find out if there are any records. If not, then append a new, blank record of whatever values you deem appropriate.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Melagan]

dhookom, for my own edification, would you use something like this to find out if there are any records and add values if appropriate?

dim rstTemp as Recordset, SQLText
SQLText = "[above SQL]"

set rstTemp = CurrentDB.OpenRecordset(SQLText)

  IF rstTemp.EOF and rstTemp.BOF Then
  [COLOR=green]'How do you add new values/records to the above recordset?[/color]
  End If

~Melagan
______
"It's never too late to become what you might have been.

 

[dhookom]

I don't know what values you would want to "fudge" into which fields. I'm not sure I understand why you would want to insert a record where there are no records to insert...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Melagan]

I would just run the above query. Then use code to find out if there are any records. If not, then append a new, blank record of whatever values you deem appropriate.

Hence, if there are no records in the given recordset, add the fudge via code.


I'm not sure when I'd ever use a technique like this in this particular context, but I'm chiefly interested in learning how to append or update records to a table through code given certain criteria on an open recordset, this example being rstTemp.EOF and rstTemp.BOF (no records).

Perhaps you'd want to add a record to a log table saying "this code was run at this time on this date and the existance of a recordset was false"

So you have table "tblLog" with fields TlogID (key), TlogDateTime, and TlogResult (yes/no).

psuedo-code:

IF rstTemp.EOF and rstTemp.BOF Then
[COLOR=green]'How would you append values to tblLog, 
'so TlogDateTime = Now() and TlogResult = False?[/color]
ELSE
[COLOR=green]'TlogDateTime = Now() and TlogResult = True[/color]
END IF

~Melagan
______
"It's never too late to become what you might have been.

 

[dhookom]

you can append a record to a table with code like:

Dim strSQL as String
strSQL = "INSERT INTO tblLog (TlogDateTime, TlogResult) " & _
    "Values(#" & Now() & "#,0)"
DoCmd.RunSQL strSQL

You might want to add code like DoCmd.SetWarnings False and later DoCmd.SetWarnings True to stop confirmation prompts.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Melagan]

Right on, that answers my question fully.

~Melagan
______
"It's never too late to become what you might have been.

 

[johnwiseman]

Hi.

The reason I required this procedure is as follows.

There are six tables [Tbl Errors A] throught to [Tbl Errors F].
These tables are generated from six seperate SQL statements. Where the "Error Code E" (in the SQL below)is substitued for Error Code A to F.

SELECT [tbl Main Input].[Error Code E], Count([tbl Main Input].ID) AS CountOfID INTO [tbl Errors E]
FROM [tbl Main Input]
WHERE ((([tbl Main Input].Date) Between [forms]![frm mi]![startdate] And [forms]![frm mi]![enddate]))
GROUP BY [tbl Main Input].[Error Code E]
HAVING ((([tbl Main Input].[Error Code E])<>0));


Once I have the six tables an ACCESS SELECT QUERY [qry total Errors] pulled all the results in so I can produce a report from just one query.

SELECT [qry Errors Total Rec'd].CountOfID AS Recd, [tbl Errors A].CountOfID AS A, [A]/[RECD]*100 AS [A Ratio], [tbl Errors B].CountOfID AS B, /[RECD]*100 AS [B RATIO], [tbl Errors C].CountOfID AS C, [C]/[RECD]*100 AS [C Ratio], [tbl Errors D].CountOfID AS D, [d]/[recd]*100 AS [D Ratio], [tbl Errors E].CountOfID AS E, [e]/[recd]*100 AS [E Ratio], [tbl Errors F].CountOfID AS F, [F]/[recd]*100 AS [F Ratio]
FROM [tbl Errors A], [tbl Errors B], [tbl Errors C], [tbl Errors D], [tbl Errors E], [tbl Errors F], [qry Errors Total Rec'd];


The problem is that if any of the Tables are not updated due to their being "no records to paste" the [qry total errors] will not run. If I insert a "0" into any non reporting table then [qry total Errors] will run fine giving the desired results.

Is that as clear as muddy water?

Regards

John

 

[dhookom]

I would question the logic of having six separate tables when it seems that one table should work. Regardless, I would change your report query to a union query that "normalizes" the records:

SELECT "A" as ErrorTable, Field1, Field2, Field3, ...
FROM [tbl Errors A]
UNION ALL
SELECT "B", Field1, Field2, Field3, ...
FROM [tbl Errors B]
UNION ALL
SELECT "C", Field1, Field2, Field3, ...
FROM [tbl Errors C]
UNION ALL
'...

Then you can create a crosstab based on the union query or do whatever else is required.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]