Query Criteria Problem

[MsNaniB]

Hello all!

I'm a beginner so please bare with me. I've researched my problem a dozen different ways and can't find the exact help I need so I've come here.
I have a list of Vendors on an Actuals Table that will have vendors added to it from time to time. I also have a Vendors Table that contains the Vendor Name and it's Vendor ID. I need to be able to run a query that will show me the new vendors (by name or by ID or both) that have been added to the Actuals Table by running it against the Vendors Table. So I'm thinking I need to put "Is Null" somewhere but nothing is working when I try that..lol So far I have this in my query:

Vendors.VendorName Vendors.VendorID Actuals.VendorID
criteria: "Is Null"

In my Actuals Table "LineItem" is the PK and in my Vendors table "VendorID" is the PK. I have them linked by VendorID.

I get blanks when I run this query. Any help would be much appreciated!
Thanks,
Nani

 

[GohDiamond]

Try this in your query:
(open a query and go to SQL view, Paste the following

SELECT Actuals.LineItem, Actuals.Vendor_Name, Actuals.Vendor_ID, Vendors.VendorID, Vendors.VendorName
FROM Actuals LEFT JOIN Vendors ON Actuals.Vendor_ID = Vendors.VendorID
WHERE (((Vendors.VendorID) Is Null));

This selects the New Vendor IDs from the Actuals Table that are not in the Vendors Table yet.

Cheers
Goh

 

[MsNaniB]

Hi Diamond! Thank you sooo much for that "SQL speak"...lol..that is so cool! I've had to make a few changes to my tables so I then attempted to try and make those changes match what you gave me and I'm coming up with blanks..lol
I'm trying so hard to figure this out myself but I'm stuck

Ok, so here is what I have pasted in SQL View right now:

SELECT Actuals.LineItem, Actuals.VendorID AS Expr1, Vendors.VendorID, Vendors.VendorName
FROM Vendors INNER JOIN Actuals ON Vendors.VendorID=Actuals.[VendorID]
WHERE (((Vendors.VendorID) Is Null));

Changes: "Actuals.VendorName" was taken out of my table because all my boss wants is VendorID. So you can see where I put in "Actuals.VendorID AS Expr1" to replace it.

So if a vendor is added to the Actuals table, it will only be its Vendor ID...which I need to show up when I run the query. The Vendors table still has the VendorName and VendorID.

The Vendors Table's PK is the VendorID and the Actuals table's PK is LineItem (It will change to something more significant later).

In my "relationships" I have these 2 tables connected from VendorID (in the Vendors table) to VendorID (FK in the Actuals Table). Is this incorrect?

I tried to test my query by adding a new Vendor ID to the Actuals table that is not in the Vendor's table..nothing happened..I'm getting blanks so I know something is wrong.

Thank you so much for taking the time to help me...I really appreciate you!
Nani

 

[GohDiamond]

You renamed Actuals.VendorID as Expr1
Take that part out.

and remove the brackets from the Inner Join Line: Actuals.VendorID

 

[MsNaniB]

Ok, thanks again Goh! So my query fields look perfect now:

Actuals.VendorID Vendors.VendorID VendorName

...and this is what my SQL looks like:

SELECT Actuals.LineItem, Actuals.VendorID, Vendors.VendorID, Vendors.VendorName
FROM Vendors INNER JOIN Actuals ON Vendors.VendorID=Actuals.VendorID
WHERE (((Vendors.VendorID) Is Null));

However, when I test out my query (adding a fake Vendor ID to the Actuals Table) it does not show up in my query like it's supposed to. Any ideas?

Nani

 

[GohDiamond]

Look at my example again:
FROM Actuals LEFT JOIN Vendors ON Actuals.Vendor_ID = Vendors.VendorID
But you have
FROM Vendors INNER JOIN Actuals ON Vendors.VendorID=Actuals.VendorID

Those are different, Mine works as you've described your desired results

Retry, Abort, Cancel ?

Cheers!
Goh

 

[MsNaniB]

Hi Goh! Thank you so much for your help and your patience..I really appreciate it. I fixed the SQL and now I'm getting all the vendors to show up that are in my Actuals table, so that's good. However, when I add a new vendor ID to the Actuals table, I need it to show up over in the Vendors.VendorID column but it's only showing up in the Actuals.VendorID column because of course I added it in the Actuals table. So this is what my query looks like, I have added the fake vendor ID, 123456:
Actuals.VendorID / Vendors.VendorID / VendorName
042186
042187
042188
123456

Thank you again!
Nani

 

[MsNaniB]

P.S. - I'm also getting a "Enter Parameter Value on Actuals.Vendor_ID" message when I run the query. I hit "ok" and it works but I know that I shouldn't be getting that, correct?

 

[GohDiamond]

Glad to hear you've had some success.

If only one of the Actuals.VendorID is new and there is nothing showing up under Vendors.VendorID then we'll need to have a look at your SQL statement again with the changes you've made. Can you post it?

That may also reveal what's going on with the request for some data entry in Actuals. Vendor_ID

Goh

 

[Coleman984]

I tried to adapt what was posted by this user to suit my needs. This is what I came up with along with the error I'm getting.


Run-time error '-2147217908 (80040e0c)':

Command text was not set for the command object

    Crt1 = "ABS"

    strSQL = "SELECT  Weekof17July2011.EmployeeName, Weekof17July2011.EmployeeID, Weekof17July2011.Sunday," & _
"Weekof17July2011.Monday, Weekof17July2011.Tuesday, Weekof17July2011.Wednesday, Weekof17July2011.Thursday" & _
"Weekof17July2011.Friday,Weekof17July2011.Saturday" & _
"FROM Weekof17July2011" & _
"WHERE (((Weekof17July2011.Sunday) Crt1))"

 

[MsNaniB]

Good Morning, Goh!

Here is my SQL statement:

SELECT Actuals.AutoNumber, Actuals.VendorID, Vendors.VendorID, Vendors.VendorName FROM Actuals LEFT JOIN Vendors ON Actuals.Vendor_ID=Vendors.VendorID
WHERE (((Vendors.VendorID) Is Null));

Please notice that in "Actuals.Vendor_ID" the underscore probably shouldn't be there because in my Actuals field it is spelled, "VendorID". I'm sure that's why I'm getting the parameter issue, however, when I take out the underscore and spell it correctly like this, "Actuals.VendorID", then I don't get my list of Vendors from the Actuals table..I get a blank table

Thanks again for your help...
Nani

 

[GohDiamond]

Try this:

SELECT Actuals.AutoNumber, Actuals.VendorID, Vendors.VendorID, Vendors.VendorName
FROM Actuals LEFT JOIN Vendors ON Actuals.VendorID = Vendors.VendorID
WHERE (((Vendors.VendorID) Is Null));​

We may have acidentally introduced a _ in the Actuals.VendorID earlier in my original example.

Goh

 

[MsNaniB]

Hello again!

I tried your new SQL statement and my query came up blank. But if I put that underscore back in, I still get the parameter msg but at least I get my vendors list...lol...I'm going to pull my hair out!

 

[MsNaniB]

P.S.

I just tried something and I think I may have to settle for it...
I added a new vendor ID to the Actuals table "1234567" and then I went and ran my query and although nothing else was there, "1234567" DID show up in the Actuals.VendorID column. So shall I just settle for that and just hide the other 2 columns? (Vendors.VendorID and VendorName)?

 

[GohDiamond]

I'm all for getting some results but that's not the way the query is supposed to work. If you want to post a copy of your database so that we can have a closer look I'll try to figure it the right way. Are you using version 2003? If not then can you convert it to version 2003 compatible and let us get a look at it? You don't have to leave any data in the Copy of the database just the structure.

Goh

 

[MsNaniB]

Sure...no problem! Please tell me like I'm 2 years old (lol) where to go and what to copy so that I make sure I show you what you want to see. Do you want to see the relationships between all my tables? Do you want the design view of a certain table? Oh and I'm using Access version 2002.
Thanks,
Nani
P.S. - It's a HUGE database that has about 10 tables

 

[GohDiamond]

Open a New Database and call it TEST or something like that. Save it to your desktop for convenience. Under the File Menu in the new database, use the Get External Data menu and select the main datbase as the source from which to import some items. Import the 2 tables (Actuals and Vendors) and the query. You can remove all the data from the tables except for a few records to test if you like. Close the TEST database. Upload yout TEST database to the forum, like attaching a file to email, there's a paperclip button to do that in the GO ADVANCED view. We'll have a look at it and see if we can fix it.

Goh

 

[MsNaniB]

I hope I did this right! Thanks for the awesome directions....
I'd LOVE for you to get a look at my relationships too. I tried to copy a screen shot of it and paste it here but it didn't work.

 

[GohDiamond]

OK I've looked at the tables and the Query. The query is responding exactly as it should for the way it's written. The underscore in Actuals.Vendor_ID has been removed so I haven't seen it ask for any parameters based on that.

When I enter a new VendorID in the actuals and run the Query the Actuals.VendorID which is not in the Vendors.VendorID shows up and the fields for Vendors.VendorID and Vendors.VendorName are empty as they should be because there is no match in the Vendors table.

...when I add a new vendor ID to the Actuals table, I need it to show up over in the Vendors.VendorID column but it's only showing up in the Actuals.VendorID column because of course I added it in the Actuals table.

You can't possibly get the Vendors.VendorID to show up if it's not in the Vendors table in the first place. So do you want to add this new Vendors.VendorID to the Vendors table with the VendorName? Is that what you're getting at? You could use a quick little popup form based on the current query to do that.

So what are you trying to accomplish with this now?

Cheers!
Goh

 

[MsNaniB]

Good Morning, Goh!
Thank you so much for taking the time to help me. I understand what you're saying and you're right.
I have a slight change that I need help with. I just found out that the "Actuals" table is going to go away and the table that will be replacing it is called "YTD Actuals Detail", which I've already created, so it exists. So, I need this query to show me new vendor names (instead of Vendor ID)added to the "Vendors" table AND new vendors added to the "YTD Actuals Detail". Can you help me with that SQL statement, please?

Sincerely,
Nani