Access SQL question

[Guest]

I had posted this message before and I have code that we cannot get to work
in Access, it will not insert the slots - what is wrong?

Newbie question: I have a database where we track dispatched with two
tables, one called Slots, the other called Dispatches.
In the slots table there are 2 fields, one called Week (which hold the
Monday date for each week in the year), the other called Available (in
this field we want to manually enter a number that will tell the system
how many dispatches we can do that week). We want to automatically take
the number from the Slots.Available and enter X number of blank records
in the Dispatch table with the monday date.
How can this be done?
Then we will create a form that can be filled in with the balance of
the information.
Thanks

Code:
INSERT INTO [Schedule] ( [Schedule].[Monday], [Schedule].[Customer])

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule]) and
[Monday].[slots] >= 1

UNION ALL

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule]) and
[Monday].[slots] >= 2

UNION ALL

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule]) and
[Monday].[slots] >= 3

UNION ALL

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule]) and
[Monday].[slots] >= 4

UNION ALL

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule]) and
[Monday].[slots] >= 5;

 

[George Nicholson]

The description of your problem and the provided SQL are not in sync. Your
SQL never references tables called either Slots or Dispatches nor fields
called Week or Available. Perhaps restating the problem will help solve
your problem on your end, perhaps not. It will however make it a lot easier
for any of us to try and help.

In any case, one approach might be code driven:

- Assumes existance of a saved query called qryUnmatchedMondays that returns
Monday field values that are in table Monday but not in table Schedule
(i.e., your current subquery).

Public Sub AddSlots()
' **AIR CODE**
Dim i as Integer
Dim iSlots as Integer
Dim dtmMonday as Date
Dim strSQL as string

dtmMonday = nz(dlookup("[Monday]","qryUnmatchedMondays"),0)
Do While dtmMonday <> 0
iSlots = nz(dlookup("[Slots]","Monday", "[Monday] = " &
dtmMonday),0)

If iSlots > 0 then
For i = 1 to iSlots
strSQL = "INSERT INTO Schedule (Monday, Customer) VALUES(#"
& dtmMonday & "#, '')"
CurrentDB.Execute strSQL
Next i
EndIf
' Loop again if there still UnmatchedMonday records, otherwise exit.
dtmMonday = nz(dlookup("Monday","[qryUnmatchedMondays]"),0)
Loop
End Sub

HTH,

I had posted this message before and I have code that we cannot get to work
in Access, it will not insert the slots - what is wrong?

Newbie question: I have a database where we track dispatched with two
tables, one called Slots, the other called Dispatches.
In the slots table there are 2 fields, one called Week (which hold the
Monday date for each week in the year), the other called Available (in
this field we want to manually enter a number that will tell the system
how many dispatches we can do that week). We want to automatically take
the number from the Slots.Available and enter X number of blank records
in the Dispatch table with the monday date.
How can this be done?
Then we will create a form that can be filled in with the balance of
the information.
Thanks

Code:
INSERT INTO [Schedule] ( [Schedule].[Monday], [Schedule].[Customer])

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule])
and
[Monday].[slots] >= 1

UNION ALL

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule])
and
[Monday].[slots] >= 2

UNION ALL

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule])
and
[Monday].[slots] >= 3

UNION ALL

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule])
and
[Monday].[slots] >= 4

UNION ALL

SELECT [Monday].[Monday] AS [FirstDayOfWeek] , '' AS [Customer] from
[Monday] where [Monday].[Monday] not in (select [Monday] from [Schedule])
and
[Monday].[slots] >= 5;