join query with like

[sammers101]

This worked before, not sure why it is no longer working

Here is my query
INSERT INTO [add to list] ( Series2, Publisher, comic, Qty, special )
SELECT [diamond order].Series, [diamond order].Publisher, [diamond order].comic, [diamond order].Qty, [special subs new].special
FROM [special subs new] INNER JOIN tblCustomers ON [special subs new].CustomerID = tblCustomers.fldCustomerID, [Comic Series] RIGHT JOIN [diamond order] ON [Comic Series].Series = [diamond order].series
WHERE ((([diamond order].Series) Like "*" & [orig] & "*") AND (([Comic Series].Series) Is Null) AND ((tblCustomers.delete)=False) AND ((tblCustomers.skippable)=False));


Dumbed down even this is not working:

SELECT [diamond order].Series
FROM [special subs new], [diamond order]
WHERE ((([diamond order].Series) Like "*" & [orig] & "*"));

Thanks everybody! No one ever showed me how to use access but these forums have really helped me create a wonderful program. A previous employee once told me that pen and paper was better in every way (I started creating it because I could not even read what he wrote)...He was SO wrong. I use it to track our sales, inventory, check in orders, keep track of customer's special orders and so much more.

 

[Deleted member 147267]

I'm afraid "doesn't work" doesn't help at all. Error message? No records append?
Maybe query doesn't like your inner join on one side and outer on the other?

 

[The_Doc_Man]

If by "not working" you mean that it returns nothing, then the first question in my mind is the nature of [orig]. What is it and can it ever contain leading or trailing spaces? And what is the nature of [Series]? Can IT ever contain leading or trailing spaces?

My second question is in the "dumbed down" version, you are creating a Cartesian JOIN (a.k.a. permutation JOIN) when you use FROM [special subs new], [diamond order] and that potentially can cause strange results. If I had to guess, the "dumbed down" version should give you all of the series in [Diamond Order] that have some superficial resemblance to something in [orig] - but we don't see where that matching field is located. Is [orig] coming from [special subs new]?

Finally, it looks like you are trying to join some stuff in that bigger SQL, but even THAT has a Cartesian JOIN in it to go with the formal joins. (That comma in the FROM clause that precedes [Comic Series] RIGHT JOIN marks off the fact of a Cartesian JOIN.)

To me it is clear that your problem is a comparison of likely contents of [Series] and of [orig] with regard to sizing, punctuation, and other typography attributes.

 

[sammers101]

Sorry, it currently says invalid procedure call. When I tried to simplify it, I got a message about the join expression not supported.

Series is the name of a comic series, Orig is the name of a generic subscription.

For example Series=All New Avengers
Orig = Avengers
The user wants to subscribe to everything Avengers, since the comic has the word Avengers in it, it is added to their list of subscriptions

Orig is coming from special subs new, I use design view when making my queries, so access wrote the sql code that I copied. I've uploaded the design view.

 

[The_Doc_Man]

OK, we are working with text names. Should be normal. As you point out, the design-view grid looked at your relationships and built what it built. I kind of had guessed that due to the excessive number of parentheses. At least one set of those outer () is superfluous, but the design grid usually does that to your SQL, probably just because it can.

A few things in your JPG image need to be mentioned.

First, look in the box ABOVE the design grid and note that you have disjoint objects. I.e. two things exist in that box for which there is no connecting relationship. This WILL give you a permutation JOIN. For small tables, no biggie. But for bigger tables, REAL "biggie" in TWO senses of the word. For a permutation JOIN, you have to go through EVERY POSSIBLE COMBINATION of the disjoint objects. Say you had 1000 possible series and 1000 customer subscriptions. This would lead to having to process 1,000,000 records. In a way, you are lucky that you found nothing - because it would be possible to get a lot more records than you imagined.

Second, an observation: In your Comic Series table, it APPEARS that you have a multi-valued field (MVF). This is fine as long as you don't ever try to establish a relationship or query based on that field. But the MVF does not play well with others. As long as it is left to itself, you are probably stable. The MOMENT you have to actually try to use it programmatically, you are in a world of hurt. Food for thought only at this time because it doesn't appear to have anything to do with your problem.

After looking at your JPG for a while more and rereading the thread, something struck me and it has a chance of being at least part of your problem.

Your "big" query includes that you check for a particular flag to be FALSE - tblCustomers.Delete - and this might run afoul of the Access Reserved Word list. You see, "DELETE" is a keyword, reserved for Access use, and that syntax would open the door for an attempt to look at a table PROPERTY (rather than a field) named .Delete - so it is POSSIBLE (not certain) that your problem is using a reserved word as a field name. If that is the problem, you have to change the name of that field because it will be unusable in SQL.

 

[isladogs]

In addition to the MVF and Cartesian join issues discussed by Doc, two of your four tables have no primary key field. This should be fixed as it will cause issues e.g. may prevent any update queries being run.

Also you are creating an unmatched query by left joining two tables using the Series field and filtering the field in the second table to null. Is that really what you wanted?
The Series field is also in a third table. Could you join that as well to get rid of the cartesian join? Alternatively use the 'special' field common to two unlinked tables

 

[sammers101]

Diamond order and special subs new are both queries, ID is a primary key for diamond order and special subs new is a query with three tables that all have primary keys as well. I've attached the design view for both.

I realize the tables/queries are not joined, they are joined by this statement:
Like "*" & [orig] & "*"

The MVF I have read can cause problems and do not use anymore.

Orig is just a truncated version of the subscription. The subscription is actually "Avengers (Everything)". Orig would be "Avengers"
Orig=Left([Comic Series].[Series],InStr([comic series].[series]," (")-1)

I use the filter of tblCustomers.Delete = false quite a bit in other queries so I would be surprised if that was the problem. It would also mean updating A LOT of my queries.

I could possibly get rid of the [Comic Series].[Series]=NULL What I was doing there was attempting to find only new [diamond order].[series] that had not yet been added to the Comic Series list yet

Maybe I should redo the whole thing? I have a list of subscriptions [special subs new].[orig] and I want to match that list against [diamond order].[series] using [diamond order].[series]= Like "*" & [special subs new].[orig] & "*"