APPEND QUERY GENERATING DUPLICATE RESULTS

[barry2004]

Hi guys, i have an append query as seen below:

INSERT INTO ozekismsout ( receiver, status, msg )
SELECT wompromo.SMS, message.Status, message.Message
FROM ozekismsout, purchase, message, wompromo
ORDER BY wompromo.SMS;

Problem with this query is that when it appends to the table "ozekismsout" it creates anything from 12,24 to 36 records for the same result when it should just be creating just one.

Bit of background: "wompromo" as seen above is a query which goes like this:

SELECT Customer.SMS
FROM Customer INNER JOIN purchase ON Customer.ID = purchase.[Customer ID]
WHERE (((purchase.Item) Like "Women"));

There is only one customer who matches this criteria, so why on earth does it generate the same entry numerous times (via append query into ozekismsout) instead of once?

 

[flyover789]

you are producing cartesic products by not joining the tables of your query.
As far as I see, you can at least leave the ozekismsout, purchase out of your from section.
but you most probably need to add a join between the message, wompromo.

so add the message-identifier to you wompromo-query and try:

INSERT INTO ozekismsout ( receiver, status, msg )
SELECT wompromo.SMS, message.Status, message.Message
FROM message INNER JOIN wompromo ON message.id = wompromo.msgid
ORDER BY wompromo.SMS;

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database Systems and Applications across all Business Areas[/blue]

 

[barry2004]

Hello HTH note that the same message is being sent to all recipients (cc'd)and i don't want the messages to have their own ID. Hence "wompromo" cannot have it's own message field.
The status and message fields come from another table called "Message" which contains 3 fields: Sender, Message and status.

Note in your query you have wompromo.SMS,message.id and wompromo.msgid

 

[flyover789]

although I'm not quite aware of your db-structure, try:

INSERT INTO ozekismsout ( receiver, status, msg )
SELECT wompromo.SMS, message.Status, message.Message
FROM message, wompromo
ORDER BY wompromo.SMS;

if this doesn't work: you should in most cases join the tables. what data does the field Customer.SMS contain?

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database Systems and Applications across all Business Areas[/blue]

 

[barry2004]

Thanks Flyover789, DB structure at

http://www.baybie.clara.net/schema.htm

 

[barry2004]

WORKS, THANKS FLYOVER & HTH