Return results where there is no relationship

[mattcdse]

Hi all,

I have two tables: ImageSoft and Software.

All items of software which have a type SA (Standard Application) must have an entry in ImageSoft under every Image Code.

When I add a new software title of type SA, it won't be included in ImageSoft for every Image Code and as I have a lot Image Codes I'd like to automate the addition of these new software items to the ImageSoft Table.

If I do a query with three fields: ImageSoft!ImageCode, Software!SoftwareCode and Software!Type, where Type was the criteria of "SA", the query returns the results of all the items that are already in ImageSoft and not those that aren't. Is there a way to return all those that are not in ImageSoft and not return those that are? I can the use these results to append them to the ImageSoft table and therefore ensure that all type SA software items are matched with each Image Code in ImageSoft.

Cheers,

Matt

 

[Brianwarnock]

Doesn't the unmatched query wizard show you how to do that?

Brian

 

[mattcdse]

Thanks for the tip Brian,

Ok, used the wizard and fiddled with the query. I can get the appropriate list of Software that does not have a relation ship with the Image Codes, however there are no list of image code returned. i.e. If sofware A does not have a relationship with Image Codes 1,2 and 3 I don't get a list that shows A1,A2 and A3, I just get A. Is there anyway I can get the list of Image codes too? I should mention that the table of image codes only has two fileds which are a compound Primary Key.

Cheers,

Matt

 

[Brianwarnock]

I must be missing something here, If there is no relationship between A and the Image Codes how can they be returned?

I think you need to show some sample data can you zip and attach a small sample db?

Brian

 

[Brianwarnock]

This has been bugging me. I presume the 2 fields in Image Codes are Imagecode and SoftwareCode. There are many ImageCodes say 20 and if you add a new softwaretype SA then you want 20 records created with ImageCode SoftwareCode? Presumably if you add a new Imagecode you would want all the softwarecodes for type SA software to be allocated this ImageCode?

If I am on the right track then

query1 create list of imagecodes by grouping on them, single field query.

query2 query1 and table Software , no join criteria "sa" but do notcheck show for softwaretype select softwarecode and imagecode, this will create a list of all Sa software and Imagecodes.

Query3, the one you run. In the design grid first select query2 then ImageCode table, join on imagecode and softwarecode but change join type to Left join (option2 in the list)
select both fields from query2, and the imagecode from the table with criteria is null .
from query type select append ,delete the append to field in the imagecode from table column. The output from this query will append records to your table for all sa software that is not present with 1 to all image codes.

Of course I may be wayout on thinking what you want.

Brian

Phew its easier to do than describe

 

[RV]

Matt,

you a NOT EXISTS construction:

SELECT *
FROM Software
WHERE Software.Type = 'SA'
AND NOT EXISTS
(
SELECT *
FROM ImageSoft
WHERE ImageSoft.SoftwareCode = Software.SoftwareCode
)

assuming you join both tables on SoftwareCode.

Don't use Type as a name for objects as it's a reserved word in Access.

RV

 

[mattcdse]

Close but no cigar

Hi all,

Brian and RV - Sorry I haven't responded earlier but I am on my hols at the mo and don't have easy access to the internet. I have tried both your ideas but sadly to no avail but thanks for the help.

Brian - Query 2 has the problem that when you run a query on unjoined objects they just simply make a kits of everything therefore both those software codes that appear in the Image Codes table and those that don't are included in the output from this query. Therefore when you run qury 3, no null results are returned.

RV - At first this seemed to be the answer, but as soon as you have at least one Image Code paired with a Software code of type SA (and none of the other Image Codes are) you've created an instance of the relationship where imagesoft.softwarecode = software.softwarecode and therefore returns a true value and therefore is not included in the output of the query. Unfortunately this is something that can happen quite regularly as sometimes we trial an application on one image for a few months (therefore it is a Non-Standard Application or NA) before rolling it out onto all the other images. Therefore when we change the type from NA to SA, you're method doesn't pick up this piece of software up as missing as the relationship is true in at least one case. (Which is really quite frustrating!)

Again thankyou both for your help. I did come up with an answer but it's likely to cause corruption or issues if there any modifications. All I did was to make a query using the two tables mentioned, delete the join and select the image code from the image code table, the software code from the software tabe and Type="SA" from the Software table. Since there is no join, a list of all the image codes with all the SA Software codes is created. I then append all these records knowing that those that already exist will create a Primary key Violation and not be added. This is messy and inaccurate and I think a good way to potentially screw up the database. Therefore I would be very great if any further ideas were put forward.

Thankyou very much again to Brian and RV for your ideas , all though you guys didn't quite get the result I need, I have learnt a lot from troubleshooting them. I hope we canget a result soon.

Cheers,

Matt

 

[Brianwarnock]

Hi Matt, I said that it was easier to do than explain so take alook at the attched and see if it is what you want.


What you did was similar to where I was with query2, query1 + Software table avoids duplication in those to be added and the use of Is Null in query3 avoids duplicating what is already there, it will allow for the addition of new imagecodes, ie adding all SA software to that image code, or if you put in a different check in query2 then anything is possible.

Brian

PS have a good Holiday

 

[mattcdse]

Thanks Brian,

Sorry for the tardy reply, but my holiday rather took over. Your code worked perfectly and now everything is cool. Thanks very much for you help.

Cheers,

Matt

 

[Brianwarnock]

Thanks Brian,

Sorry for the tardy reply, but my holiday rather took over. Your code worked perfectly and now everything is cool. Thanks very much for you help.

Cheers,

Matt

That's what holidays are for , glad it all worked out, and thanks for the response.

Brian