Make table with no data (i.e. dummy value)

[Noah114]

I currently have 3 queries (1 make table, 2 append) that filter out utilization from a master table. The first query searches for the mail network and makes a new table, the other two queries append to the new table segregating two other networks. The queries simply categorize data by network and sum sales, margin, etc... However, is there a way if one of the networks has no utilization to still create a make table or append with a zero value? I did not see anything in the query settings?

 

[mikey69]

Probably easiest to do this in a Visual Basic module. There is a DAO method called "CreateTabledef", which does what you want, without the need to add records. Here's some code which does just that. In this case, it deletes an existing table first, because it's quicker to do this than to delete all records in an existing table:
Sub CreateLocalTable()
Set DB = CurrentDb
'*** Delete local table
DB.TableDefs.Delete "tblOrderedReceived"
'*** Create local table
Set TD1 = DB.CreateTableDef("tblOrderedReceived")
With TD1
.Fields.Append .CreateField("PurchOrdItemId", dbLong)
.Fields.Append .CreateField("Description", dbText)
.Fields.Append .CreateField("OrderedQ", dbSingle)
.Fields.Append .CreateField("UnitPrice", dbCurrency)
.Fields.Append .CreateField("ReceivedQ", dbSingle)
.Fields.Append .CreateField("OutstandingQ", dbSingle)
.Fields.Append .CreateField("CommittedValue", dbCurrency)
End With
DB.TableDefs.Append TD1

End Sub

Access makes all things possible. It even makes them intelligible