Solved

Microsoft Access 2007 Query Help

Posted on 2012-04-05
37
509 Views
Last Modified: 2012-04-18
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!!
0
Comment
Question by:mmcgillo88
  • 18
  • 18
37 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 37814450
Try switching your query to sql view and. Writing


SELECT * FROM Yourtable
WHERE VendorName IN
("Name1", "name 2", etc)
0
 

Author Comment

by:mmcgillo88
ID: 37814451
How do you access SQL view in Microsoft Access 2007?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814455
Right click the window in the query builder and select Sql view.
0
 

Author Comment

by:mmcgillo88
ID: 37814458
What if a name is not exact but I want to find one that is "similar"?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814466
SELECT * FROM Yourtable
WHERE yourfield LIKE "abc*"

Will find anything beginning with abc.

The wildcard * can be moved as needed.
0
 

Author Comment

by:mmcgillo88
ID: 37814471
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37814474
Some examples

LIKE "*abc*"          contains abc

LIKE "*abc"             ends with abc

Etc
0
 

Author Comment

by:mmcgillo88
ID: 37814477
The comma is not working, how do I string them together. I understand the wildcard option.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814491
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
 

Author Comment

by:mmcgillo88
ID: 37814497
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37814518
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
 

Author Comment

by:mmcgillo88
ID: 37814529
Thanks for the continued help mbizup. The error message appearing is syntax error (missing operator) in my IN syntax?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814531
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
 

Author Comment

by:mmcgillo88
ID: 37814532
Does the list of exact names need to be in quotes or just parenthesis?
0
 

Author Comment

by:mmcgillo88
ID: 37814534
How do I put just those vendor names in a separate table? Thank you, mbiz.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814537
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
 

Author Comment

by:mmcgillo88
ID: 37814610
How do I like the new table to the table with all of the data that I need?
0
 

Author Comment

by:mmcgillo88
ID: 37814686
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 61

Expert Comment

by:mbizup
ID: 37814691
Can you give examples of your "Like" data (Generic examples are fine if the data is sensitive)
0
 

Author Comment

by:mmcgillo88
ID: 37814699
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37814704
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
 

Author Comment

by:mmcgillo88
ID: 37814709
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37814731
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
 

Author Comment

by:mmcgillo88
ID: 37814732
How do I tell if it worked or not? When I go back to the original table only these ones should be selected?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814755
If you look at the query in datasheet view you should be seeing records corresponding to those names and no others.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37814760
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
 

Author Comment

by:mmcgillo88
ID: 37814764
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37814785
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37814830
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
 

Author Comment

by:mmcgillo88
ID: 37815757
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
 

Author Comment

by:mmcgillo88
ID: 37815773
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37815779
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
 

Author Comment

by:mmcgillo88
ID: 37815789
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37815791
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
 

Author Closing Comment

by:mmcgillo88
ID: 37815795
VERY PATIENT, VERY HELPFUL AND VERY KNOWLEDGEABLE. ALL AROUND ANSWERED QUESTIONS!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37815805
Glad to help out!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now