Adding Records with a Query?

[RussCRM]

I work for a small homeless shelter. I have a table called "Services"
that contains records with a ServiceID, GuestID, ServiceDate, and
ServiceType. The total number of records is the total number of
services we have provided. Such as:

ServiceID GuestID ServicesDate ServicesType
1875 4572 07/31/07 Men's Dorm
1876 9762 07/31/07 Dinner
1877 9762 07/31/07 Men's Dorm


I want to find a way to go through and add a record for a "Dinner"
service provided for each record where there is a "Men's Dorm"
service.

For instance, if guest 4572 stayed in our men's dorm, then he had
dinner with us also. However, the dinner entry wasn't entered. So, I
have to go back through and add a dinner record for every guest who
stayed in our men's dorm for the same date.

So, for each record like:
ServiceID GuestID ServicesDate ServicesType
1875 4572 07/31/07 Men's Dorm

I want to add a new record such as:
1897 4572 07/31/07 Dinner '

Any ideas?

 

[Jerry Whittle]

My advice is don't bother. If the business rule states that men's dorm also
means dinner, there is no need. You could count up the men's dorm plus any
dinners that are independent of the men's dorm to get the data that you need.

Also when posting to these forums, it takes about 15 minutes to show up.
That could explain your double post on this subject.

 

[John Spencer]

ALthough I agree with Jerry, if you really feel you need to do this it can
be done.

UNTESTED SQL statement follows. SAVE your data before attempting this
query. If it messes up then revert to the saved (backup) version.
Assumption: ServiceID is an autonumber field.

INSERT INTO Services (GuestID, ServicesDate, ServiceType)
SELECT guestID, ServicesDate, "Dinner"
FROM Services LEFT JOIN
(SELECT ServiceDate, GuestID
FROM Services
WHERE ServiceType = "Dinner") as DinDin
ON Services.ServiceDate = DinDin.ServiceDate
AND Services.GuestID = DinDin.GuestID
WHERE ServicesType = "Men's Dorm"
AND DinDin.GuestID is Null
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[RussCRM]

Thanks a lot for your advice! Based on your advice with a few name
changes, this is basically what I added: (GuestID is related to/
replaced by ServicesLink)

INSERT INTO Services ( ServicesLink, ServicesDate, ServicesType )
SELECT ServicesLink, ServicesDate, "Dinner"
FROM Services LEFT JOIN
(SELECT ServicesDate, ServicesLink
FROM Services
WHERE ServicesType = "Dinner") AS DinDin
ON (Services.ServicesLink = DinDin.ServicesLink) AND
(Services.ServicesDate = DinDin.ServicesDate)
WHERE ServicesType = "Men's Dorm"
AND DinDin.ServicesLink is Null;

I run the query and get an error message saying "ServicesLink could
refer to more than one table listed in the FROM clause of your SQL
statement.

So, I edit the Select names to:

INSERT INTO Services ( ServicesLink, ServicesDate, ServicesType )
SELECT Services.ServicesLink, Services.ServicesDate, "Dinner"
FROM Services LEFT JOIN
(SELECT ServicesDate, ServicesLink
FROM Services
WHERE ServicesType = "Dinner") AS DinDin
ON (Services.ServicesLink = DinDin.ServicesLink) AND
(Services.ServicesDate = DinDin.ServicesDate)
WHERE ServicesType = "Men's Dorm"
AND DinDin.ServicesLink is Null;

Now, I get an "Invalid Argument" error.

 

[John Spencer]

OK. Let's try to break this down.

Just running the inner SELECT query and see if that works and returns
expected data.
SELECT ServicesDate, ServicesLink
FROM Services
WHERE ServicesType = "Dinner"

Does that work and return records?
If that fails works, then I would suspect that ServicesType is not a text
field but is a number field masquerading as a text field because in the
table the field is a lookup field.
If that is the case, when you open the table directly in datasheet view, you
will see a combobox displaying choices for the ServicesType. Then you will
have to figure out what the actual values are for "Dinner".

Next try:
SELECT Services.ServicesLink, Services.ServicesDate, "Dinner"
FROM Services LEFT JOIN
(SELECT ServicesDate, ServicesLink
FROM Services
WHERE ServicesType = "Dinner") AS DinDin
ON (Services.ServicesLink = DinDin.ServicesLink) AND
(Services.ServicesDate = DinDin.ServicesDate)
WHERE ServicesType = "Men's Dorm"
AND DinDin.ServicesLink is Null;

Does that work and return records?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[RussCRM]

OK. Let's try to break this down.

Just running the inner SELECT query and see if that works and returns
expected data.
SELECT ServicesDate, ServicesLink
FROM Services
WHERE ServicesType = "Dinner"

Does that work and return records?
If that fails works, then I would suspect that ServicesType is not a text
field but is a number field masquerading as a text field because in the
table the field is a lookup field.
If that is the case, when you open the table directly in datasheet view, you
will see a combobox displaying choices for the ServicesType. Then you will
have to figure out what the actual values are for "Dinner".

Next try:
SELECT Services.ServicesLink, Services.ServicesDate, "Dinner"
FROM Services LEFT JOIN
(SELECT ServicesDate, ServicesLink
FROM Services
WHERE ServicesType = "Dinner") AS DinDin
ON (Services.ServicesLink = DinDin.ServicesLink) AND
(Services.ServicesDate = DinDin.ServicesDate)
WHERE ServicesType = "Men's Dorm"
AND DinDin.ServicesLink is Null;

Does that work and return records?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Worked great! Thanks again!!!

 

[John Spencer]

Just out of curiousity. What did you do to make it work?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[RussCRM]

Just out of curiousity. What did you do to make it work?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I did an append query in SQL view with this text to add records for
the month of July:

INSERT INTO Services ( ServicesLink, ServicesDate, ServicesType )
SELECT Services.ServicesLink, Services.ServicesDate, "Dinner" AS Expr1
FROM Services LEFT JOIN [SELECT ServicesDate, ServicesLink
FROM Services
WHERE ServicesType = "Dinner"]. AS DinDin ON
(Services.ServicesDate = DinDin.ServicesDate) AND
(Services.ServicesLink = DinDin.ServicesLink)
WHERE (((Services.ServicesDate) Between #7/1/2007# And #7/31/2007#)
AND ((Services.ServicesType)="Men's Dorm") AND ((DinDin.ServicesLink)
Is Null));

Worked just like I needed it to! Thanks again!