Complex Query

[zepter2000]

Thats my situation:
I have three tables:
1. Main table: tblDB_Daten
Fields: [ID], [Title], [PublisherLocation], [PublisherName], [ISBN]
2. Table containing the information for the query: tblMark_PV
Fields: [ISBN], [PubLocSingle], [PubLocCombin], [PubName]
3. Table for the result of query: tblDB_PV
Fields: [ID_Daten], [PV]

As a result the fields [tblDB_PV].[PV] in the "result-table" should be filled with the text "PV" in case
1. Field [tblDB_Daten].[ISBN] in Main-table contains [tblMark_PV].[ISBN] from the "query-table"
2. Field [tblDB_Daten].[PublisherLocation] in Main-table contains [tblMark_PV].[PubLocSingle] from the "query-table"
3. Field [tblDB_Daten].[PublisherLocation] in Main-table contains [tblMark_PV].[PubLocCombin] from the "query-table"
and additionally
Field [tblDB_Daten].[PublisherName] in Main-table contains [tblMark_PV].[PubName] from the "query-table"

I have tried for days to generate a working SQL-Code (using UPDATE and LEFT JOIN) but failed. So I am asking for some help now.

[jforbes]

What does your current query look like?

[NeoPa]

Is the "query-table" a reference to [tblDB_PV]?

In general you've included all the important information, but I'm sorry to say that it doesn't make clear sense (Certainly not to me at least). I expect something's been lost in translation so I have to say you've done a good job, but if you could make it clearer that would be very helpful.

As JForbes says, it would also be very helpful, and allow us to help you more, if you were to post in what you have got to so far in the way of your SQL.

I can say that you'll need JOINs between at least two tables. How those JOINs would work I'm not clear on yet without a better understanding of what you're trying to do.

[zepter2000]

Thnks for the replies!
I start a new trial:
tblDB_Daten contains a set of data, that will be imported from an external source once a week. From this dataset the following information should be extracted: Is the ISBN from a publisher located in Bavaria, is the publishers location in Bavaria. As the location is not always distinct, an additional query is necessary: Location im combination with publishers name.
The relevant information for the query is in tblMark_PV.
I indend to write the query result in a seperate table tblDB_PV.
There also is a query-table with the data from tblDB_Daten and hopefully the data from tblDB_PV.
As the user should have the choice of the criterias (ISBN, location ... etc), I thoght this seperate table tblDB_PV might be helpfull.
By now I fail even with the first query-step.

Dim s As String
' Transfers the IDs from tblDB_Daten to tblDB_PV
    Set db = CurrentDb
        s = "UPDATE tblDB_PV AS P " & _
        "RIGHT JOIN tblDB_Daten AS D " & _
        "ON D.[ID]=P.[ID_tblDB_Daten] " & _
        "SET P.[ID_tblDB_Daten] = D.[ID] "
    db.Execute s
' Should fill tblDB_PV with "PV" according to tblMark_PV
    s = "UPDATE tblDB_PV AS P " & _
        "RIGHT Join (tblDB_Daten AS D " & _
                   "LEFT JOIN tblMark_PV AS M " & _
                   "ON D.ISBN=M.[PV_ISBN]) " & _
       "ON D.ID=P.[ID_tblDB_Daten] " & _
        "SET P.[DB_Daten_PV] = 'PV' " & _
        "WHERE D.[ISBN] LIKE '" & M.[PV_ISBN] & "*'"
    db.Execute s
    Set db = Nothing
 

Here comes error 424 "object required".
Im am quite discouraged and very thankful for some help.

[jforbes]

I'm pretty sure the first Update wont do anything as it is updating with the same fields that are in the Join. If you are expecting it to create records, you will want an Insert Query.

The second Update Joining looks suspect as the Alias D is inside the Temp Table. I think this is more like what you are looking for:

' Should fill tblDB_PV with "PV" according to tblMark_PV
s = "UPDATE tblDB_PV AS P " & _
    "INNER JOIN tblMark_PV AS M " & _
    "ON M.ID=P.[ID_tblDB_Daten] " & _
    "INNER JOIN tblDB_Daten AS D " & _
    "ON D.ISBN=M.[PV_ISBN] " & _
    "SET P.[DB_Daten_PV] = 'PV' " 

I changed the Join Type to Inner as I think that is really what you are after and I don't think you need the WHERE as this is being taken care of by the Join. (Inner will only return records where there are records found on both sides of the Join)

A lot of this is just guess work and if it isn't right, let us know.

Thinking about the big picture, I'm not sure why you are doing all this. It's possible you could create a single select query to return all the information that you need without updating tables. Usually, in a situation where there is a data file being supplied on a regular basis, it's easiest to run an insert query to grab a copy the data into a history type table, then use a select query to format the history type table for whatever other function/form needs to use it.

[zepter2000]

Yes the first UPDATE is unnecessary, if I use INSERT INTO instead. (I forgot about that.)
Your advice about the "history type table" is very helpful. So it should also be possible to export a table, that contains all the additional information.
For the first it is a pity, your code does not work. I need the WHERE/LIKE as only the first numbers of the ISBN are relevant. I tried with modifying: Using brackets for Access, RIGHT JOIN and so on. As there is no correlation of M.ID with P.[ID_tblDB_Daten], changed with M.[PV_ISBN]=P.[DB_Daten_PV]. But nothing works.
By the way my original code works with a string in the WHERE.

    s = "UPDATE tblDB_PV AS P " & _
        "INNER Join (tblDB_Daten AS D " & _
                   "LEFT JOIN tblMark_PV AS M " & _
                   "ON D.[ISBN]=M.[PV_ISBN]) " & _
       "ON D.ID=P.[ID_tblDB_Daten] " & _
        "SET P.[DB_Daten_PV] = 'PV' " & _
        "WHERE D.[ISBN] LIKE '" & "978" & "*'"
 

When I replace with

"WHERE D.[ISBN] LIKE '*" & M.PV_ISBN & "*'"

, the "object required erros" appears.
I am very glad about further assistance.

[jforbes]

I think the trouble you are running into is that M.PV_ISBN isn't available when you are running the VBA Code, it's only available when the SQL is executed. I didn't quite catch this before as I was focusing on whether or not you would need the WHERE clause. If you really feel that you need the WHERE, you could try:

"WHERE D.[ISBN] = M.PV_ISBN "
'OR
"WHERE D.[ISBN] LIKE '*' + M.PV_ISBN + '*'"
'Or If you are attempting to pull the ISBN from
'the current Form's recordset, the syntax is something like:
"WHERE D.[ISBN] LIKE '*" & Me!PV_ISBN & "*'"

[zepter2000]

It works fine with

"WHERE D.[ISBN] = M.PV_ISBN"
 

So "M.PV_ISBN" should be available. It is the LIKE in combination with a "Field-Query", that fails.
Du You have an alternative for the WHERE/LIKE clause? As I said, only the first part of the ISBN is relevant.

[jforbes]

I think this will work for you, maybe:

"WHERE D.[ISBN] LIKE '*' & M.PV_ISBN & '*'"

or better yet:

"WHERE D.[ISBN] LIKE M.PV_ISBN & '*'"

I apologize for not providing this earlier, I was mixing and matching SQL Server and MS-Access syntax.

[zepter2000]

Thanks for your engagement.
When I try this Code there is no errror message, but it gives back all titles thal have an ISBN. So for me it looks as if the "M.PV_ISBN" is empty. Thats only the wildcard "'*" does the job.
Surprisingly "M.PV_ISBN" is correctly filled when the clause ""WHERE D.[ISBN] = M.PV_ISBN"" is used.
Possibly its time to give up and engage with your suggestion of a history type table. Otherwise I`d like to understand at least.

[NeoPa]

Hi.

Please post that last explanation within the context if you would. It doesn't seem to make sense as you've expressed it so it's hard to understand what you mean.

IE. Show both the working and the non-working WHERE clauses in full, in separate code windows, and with the results of each explained.

That would be very helpful.

[zepter2000]

Thanks for your offer.
I managed by modifying like this:

    s = "UPDATE tblDB_PV AS P " & _
        "RIGHT Join (tblDB_Daten AS D " & _
                   "RIGHT JOIN tblMark_PV AS M " & _
                   "ON D.[ID] > M.[ID] OR D.[ID] <= M.[ID]) " & _
        "ON D.[ID]=P.[ID_tblDB_Daten] " & _
        "SET P.[DB_Daten_PV] = 'PV' " & _
            "WHERE  (D.[ISBN] LIKE ('*' &  M.[PV_ISBN] &'*')  )"
 

Presumably it did not work because of "ON D.[ISBN]=M.[PV_ISBN])" or either "ON D.[ID]=M.[ID])" in my original code, as there are no identical ISBNs or IDs in the "D-table" and the "M-table".