Microsoft Access 2007 Query Help

Microsoft Access 2007 database help, I know nothing about queries or using the database/table so very beginners language would be much appreciated. I have a database already created and I need to run a query (I believe) based on certain criteria. For my one column named "VendorName" I want to select 25 specific vendors to research. I tried to write the names in the "Criteria" section of my query (under {VendorName] column) but it ran out of room. Please advise!!
mmcgillo88Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Oops - missed your last comment.

Look at the navigation pane in the left hand sidebar then:

- Right click your query's name
- Click "Export"
- Click "Excel"
0
 
mbizupConnect With a Mentor Commented:
Try switching your query to sql view and. Writing


SELECT * FROM Yourtable
WHERE VendorName IN
("Name1", "name 2", etc)
0
 
mmcgillo88Author Commented:
How do you access SQL view in Microsoft Access 2007?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
mbizupCommented:
Right click the window in the query builder and select Sql view.
0
 
mmcgillo88Author Commented:
What if a name is not exact but I want to find one that is "similar"?
0
 
mbizupCommented:
SELECT * FROM Yourtable
WHERE yourfield LIKE "abc*"

Will find anything beginning with abc.

The wildcard * can be moved as needed.
0
 
mmcgillo88Author Commented:
After I finish writing this in my query do I need a closing sequence? Do you have a link that could teach me more about SQL? what happens when I write this SQL and then run my query, will my results appear? I want to find the 25 vendors based on VendorName and then pull only certain columns of information for them...

This is what I have in SQL

SELECT tblInvoicesSubmitted.[VendorName], tblInvoicesSubmitted.[StatusDesc], tblInvoicesSubmitted.[LineItemAmount], tblInvoicesSubmitted.[AdjustedAmount], tblInvoicesSubmitted.[InvoiceTotal]
FROM tblInvoicesSubmitted
WHERE [VendorName] LIKE
("VendorA", "VendorB", "VendorC", "VendorD", "VendorE")
0
 
mbizupCommented:
Some examples

LIKE "*abc*"          contains abc

LIKE "*abc"             ends with abc

Etc
0
 
mmcgillo88Author Commented:
The comma is not working, how do I string them together. I understand the wildcard option.
0
 
mbizupCommented:
You cant use LIKE with a list like that.  The keyword IN works with that syntax.

Im currently typing on a cellphone so cant type in the detail needed here.

I'll step back for others to respond and will check back in a couple hours when I have areal computer.
0
 
mmcgillo88Author Commented:
When I have IN can I have a couple of them that have the wildcard option? so continue the comma and parenthesis SQL but then a few of them I am not sure of the exact full name in the database so for a few could I keep the wildcard * at the end? Thanks.
0
 
mbizupCommented:
The syntax would look similar to this ...


WHERE yourfield In (the list of exact names)  OR
Yourfield Like "abc*"

You'd list exact matches using IN and handle the 'similar'  data using LIKE.
0
 
mmcgillo88Author Commented:
Thanks for the continued help mbizup. The error message appearing is syntax error (missing operator) in my IN syntax?
0
 
mbizupCommented:
Another alternative is to create a seperate table to store just the names you want to report .

 Then you could look up the names that you want to report as criteria for your query


SELECT *
FROM yourtable
WHERE VendorName IN
(SELECT Vendorname FROM Yourothertable )
0
 
mmcgillo88Author Commented:
Does the list of exact names need to be in quotes or just parenthesis?
0
 
mmcgillo88Author Commented:
How do I put just those vendor names in a separate table? Thank you, mbiz.
0
 
mbizupCommented:
You to manually type in the vendors that you went to report into a table.

 That may be the better approach if you have 25 names that y.ou want to report.
 that list of names could easily be modified when needed.
0
 
mmcgillo88Author Commented:
How do I like the new table to the table with all of the data that I need?
0
 
mmcgillo88Author Commented:
I need help, again, please. How do I use SQL to pick 25 vendor names, three of whcih are "like" but may not be exact"
0
 
mbizupCommented:
Can you give examples of your "Like" data (Generic examples are fine if the data is sensitive)
0
 
mmcgillo88Author Commented:
Thanks for following up, mbiz. I don't need the "like" data anymore, I have all the vendor names I need (25) exact matches. I jsut need a query that selects them from the table (my original table) and puts them in a new table. Currently I have SELECT FROM WHERE IN and that is it.
0
 
mbizupCommented:
Just to make sure we are thinking on the same plane -

What is your end goal?  ie: what do you want to do with the data once you select records matching those 25 names?
0
 
mmcgillo88Author Commented:
I want to extract the data from the master table, I'm comparing this data to an Excel report I have. I have a column named "Adjusted Amount" my goal would be to get a count of the adjusted amount for these 25 names. If that makes sense.
0
 
mbizupCommented:
Does this work for your exact names?

SELECT tblInvoicesSubmitted.[VendorName], tblInvoicesSubmitted.[StatusDesc], tblInvoicesSubmitted.[LineItemAmount], tblInvoicesSubmitted.[AdjustedAmount], tblInvoicesSubmitted.[InvoiceTotal]
FROM tblInvoicesSubmitted
WHERE [VendorName] IN
("VendorA", "VendorB", "VendorC", "VendorD", "VendorE")
0
 
mmcgillo88Author Commented:
How do I tell if it worked or not? When I go back to the original table only these ones should be selected?
0
 
mbizupCommented:
If you look at the query in datasheet view you should be seeing records corresponding to those names and no others.
0
 
mbizupCommented:
The query displays a selected subset of your table's data based on the criteria given, but it does not affect the data in your original table.
0
 
mmcgillo88Author Commented:
It looks like it pulled the data (and then some extra data and names?) in my original table? But if I delete this query then I have my original table? How do i get it so I haev my original table and this data pulled into two separate tables? Does that make sense?
0
 
mbizupCommented:
Okay -


<<But if I delete this query then I have my original table?>>

Correct.  The query is something that just 'shows' you the data in your table without actually modifying it.

If you save that query, then you can right-click it's name in the navigation pane and export it to Excel if desired - where you can manipulate the data as needed without affecting the original table.

Alternatively, If you want to create a separate table for your selected data then look at your query in design view (not SQL view) and

- Right-click the query window
- Select "Query Type"
- Then select "Make Table Query"

You will be prompted for a name for the table you will be creating from the selected data.

Once you run the Make Table Query, you will have a new table with just the data you selected.
0
 
mbizupCommented:
Just a note on this comment:

<<
The query is something that just 'shows' you the data in your table without actually modifying it.
>>

While a SELECT query itself does not affect your original table, if you delete or modify the data shown in the query (this is different from deleting the query itself from the navigation pane) - you are also doing the same to the data in your original table (it is the same data)
0
 
mmcgillo88Author Commented:
Thank you for the continued help. My query still does not seem to be working. This is what I have. Please help, thank you.

SELECT tblInvoicesSubmitted.[VendorName], tblInvoicesSubmitted.[StatusDesc], tblInvoicesSubmitted.[LineItemAmount], tblInvoicesSubmitted.[AdjustedAmount], tblInvoicesSubmitted.[InvoiceTotal]
FROM tblInvoicesSubmitted
WHERE [VendorName] IN
("VendorA", "VendorB", "VendorC", "VendorD", "VendorE")

<Edited by SouthMod to remove names>
0
 
mmcgillo88Author Commented:
Actually, it has seemed to work. Thank you. Will have more questions downt he road, but could you help me Export this to Excel?
0
 
mbizupCommented:
What are the exact issues with it?  The overall syntax looks good.

For example:

- Not pulling the correct data
- Pulling unexpected data

Post specific examples showing what you are expecting vs what you are actually getting, if possible.

Posting a sample database containing just the relevant table and query may also be helpful.
0
 
mmcgillo88Author Commented:
When I try to export to Excel (I have 300,000 rows) it says it is too large to be copied over to Excel. How do I break it up so I can copy onto one Excel worksheet? Thanks again.
0
 
mmcgillo88Author Commented:
VERY PATIENT, VERY HELPFUL AND VERY KNOWLEDGEABLE. ALL AROUND ANSWERED QUESTIONS!
0
 
mbizupCommented:
Glad to help out!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.