can i have a querie with a linked table

[Guest]

I have been asigned the task of figuring out how to use access. I have what
i think is successfully having a linked table to a master excell spread
sheet. now when i go to set up a querie it freezes on me. can i have a
querrie with a linked table or no. because it lets me set up a querie and run
it without a linked table.

 

[Guest]

Yes, you can query an Excel linked table. BUT, the linked table may see more
records than you. Be sure to limit the records by setting a field criteria
(like KeyField is Not Null) so blank rows in Excel are not used as records
(and make your query seem to 'hang' when it is busy processing 65,536 rows!)

Of course, if you are moving away from Excel, things will work better if you
import the worksheet and work with forms and reports within Access.

- Dick D

 

[John Vinson]

I have been asigned the task of figuring out how to use access. I have what
i think is successfully having a linked table to a master excell spread
sheet. now when i go to set up a querie it freezes on me. can i have a
querrie with a linked table or no. because it lets me set up a querie and run
it without a linked table.

You can query linked tables - it's perfectly routine in fact. You
cannot *update* Excel files (with the current release of Access) but
you should be able to query them!

Please open your query in SQL view and post the SQL text here;
indicate what you expect to see and something about the spreadsheet
you're querying.

John W. Vinson[MVP]

 

[Guest]

well it wont even let me create the querry. while i am in wizard and i click

 

[John Vinson]

well it wont even let me create the querry. while i am in wizard and i click
on finish querrie so i can then go to modify in design it freezes and shuts
down so i cant even get to that point

Hm. Can you see other (local) tables in this database? can you create
queries on those tables? Can you view the linked excel data as a table
datasheet (just doubleclick to open it)? Can you create a query
without using the wizard?

You may want to delete the link; compact and repair the database;
relink the spreadsheet, and try again. Something is clearly messed up!

John W. Vinson[MVP]

 

[Guest]

Yes i can open the linked table and i can create a querie as far as i know in
design but it doesnt give me enough categories to use with out using the
wizard so i cant manually input them rather than in wizard import categories
over. Also how would i restrict the parameter in excell before i link it.

hutchinson180

 

[John Vinson]

Yes i can open the linked table and i can create a querie as far as i know in
design but it doesnt give me enough categories to use with out using the
wizard so i cant manually input them rather than in wizard import categories
over. Also how would i restrict the parameter in excell before i link it.

I'm sorry, I don't understand.

"Enough categories" - enough fields? enough Criteria rows? What do you
mean by "category" in this context?

Excel is not a database; I don't know how you would "restrict the
parameter" in Excel, other than creating a new worksheet based on a
filter operation.

Could you explain what data you have, and what it is that you are
trying to accomplish, with some examples? There may well be an easier
way than whatever you're not attempting!

John W. Vinson[MVP]

 

[Guest]

well their is an excell spreadsheet with multiple columns. Their is data
updated in the excell spreadsheet daily here is an example:
invoice# po# Date received Date completed
1 55 01/12/2005 02/12/2005
2 69 08/26/2005 12/07/2005
etc. Now what i need to do is set up a data base that is linked to excell
where it updates when the excell work sheet updates. Now i need to be able
to set up a querie where can say pull up all items in a specific date range
ie. 01/12/2005 - 03/15/2005 and it will just pull up data in that specific
time frame. I have figured out a way to link the data base and excell
spreadsheet to update when they add info but now i cant create the querie(s)
i need to

 

[John Vinson]

well their is an excell spreadsheet with multiple columns. Their is data
updated in the excell spreadsheet daily here is an example:
invoice# po# Date received Date completed
1 55 01/12/2005 02/12/2005
2 69 08/26/2005 12/07/2005
etc. Now what i need to do is set up a data base that is linked to excell
where it updates when the excell work sheet updates. Now i need to be able
to set up a querie where can say pull up all items in a specific date range
ie. 01/12/2005 - 03/15/2005 and it will just pull up data in that specific
time frame. I have figured out a way to link the data base and excell
spreadsheet to update when they add info but now i cant create the querie(s)
i need to

Thank you, that helps.

Let's say your linked Excel table is named Invoices. You'ld link it
using File... Get External Data... Link, and using Excel in the "files
of type" option. It's necessary - I believe, I may be wrong here - to
have exclusive use of the spreadsheet at the time you're running the
query.

Create a Query in the query design window by adding the Invoices table
to the grid. Select whatever fields you want to see. On the Criteria
line underneath Date Received type

BETWEEN [Enter start date:] AND [Enter end date:]

Open the query; reply to the prompts with reasonable dates; do you get
the desired records? If not, what happens?

John W. Vinson[MVP]

 

[Guest]

thats working but now when i enter the dates to search i am getting a numeric
field overflow warning what do i do

well their is an excell spreadsheet with multiple columns. Their is data
updated in the excell spreadsheet daily here is an example:
invoice# po# Date received Date completed
1 55 01/12/2005 02/12/2005
2 69 08/26/2005 12/07/2005
etc. Now what i need to do is set up a data base that is linked to excell
where it updates when the excell work sheet updates. Now i need to be able
to set up a querie where can say pull up all items in a specific date range
ie. 01/12/2005 - 03/15/2005 and it will just pull up data in that specific
time frame. I have figured out a way to link the data base and excell
spreadsheet to update when they add info but now i cant create the querie(s)
i need to

Thank you, that helps.

Let's say your linked Excel table is named Invoices. You'ld link it
using File... Get External Data... Link, and using Excel in the "files
of type" option. It's necessary - I believe, I may be wrong here - to
have exclusive use of the spreadsheet at the time you're running the
query.

Create a Query in the query design window by adding the Invoices table
to the grid. Select whatever fields you want to see. On the Criteria
line underneath Date Received type

BETWEEN [Enter start date:] AND [Enter end date:]

Open the query; reply to the prompts with reasonable dates; do you get
the desired records? If not, what happens?

John W. Vinson[MVP]

 

[John Vinson]

thats working but now when i enter the dates to search i am getting a numeric
field overflow warning what do i do

Please open your Query in SQL view and post the actual SQL text here.
Perhaps you need to use the Parameters property of the query, but
without seeing it I'm guessing in the dark!

John W. Vinson[MVP]

 

[Guest]

can you please walk me through it

Please open your Query in SQL view and post the actual SQL text here.
Perhaps you need to use the Parameters property of the query, but
without seeing it I'm guessing in the dark!

John W. Vinson[MVP]

 

[John Vinson]

can you please walk me through it

Open the Query in design view.

Click the "View" menu option.

Select "SQL" from the dropdown list of views.

A window will open with some text (the actual language of the query).

Type Ctrl-A to copy the SQL text to the clipboard.

Type Ctrl-V to paste that text into a new message in this thread.

John W. Vinson[MVP]

 

[Guest]

what new message im sorry but i do not understand

 

[John Vinson]

what new message im sorry but i do not understand

This IS a new message. You must posted it. I'm assuming that you're
using Microsoft's webpage - open the webpage and start typing a
message, just as you did to post this one. Then do the copy and paste.

John W. Vinson[MVP]

 

[Guest]

PARAMETERS [enter start date] DateTime, [enter end date] DateTime;
SELECT [Linked Master].[PO #], [Linked Master].[Job Number], [Linked
Master].[GO/CO], [Linked Master].Engineer, [Linked Master].[Fielding Cost or
Engineer ], [Linked Master].[First Notified], [Linked Master].[Due Date],
[Linked Master].[E-Files Submitted For Approval], [Linked Master].[Meter
Type], [Linked Master].[RT Type], [Linked Master].[Service Amps], [Linked
Master].[Final Delivery], [Linked Master].[Bid Area], [Linked Master].[Job
Site Address], [Linked Master].City, [Linked Master].[Power Contact Name],
[Linked Master].Phone, [Linked Master].[Date Submitted Power App], [Linked
Master].[Power App Deposit], [Linked Master].[Date Received Power Contract],
[Linked Master].[Power Contract Charge], [Linked Master].[MLX Number],
[Linked Master].[Other Tracking], [Linked Master].[Additional Power Charges],
[Linked Master].[Permit Req'd (Y or N)], [Linked Master].[Date Submitted
Encr Permit], [Linked Master].[Date Received Encr Pemit], [Linked
Master].[Encr Permit Number], [Linked Master].[Encr Permit Charge], [Linked
Master].[Additional Encr Permit Charge], [Linked Master].F31, [Linked
Master].[Pre Fielding and/or cost est# $ to invoice], [Linked
Master].[Cancelled Job after Fielding $ to Invoice], [Linked
Master].[Engineering $ to invoice], [Linked Master].[Chg Req], [Linked
Master].[Invoiced Amount (Put Date in Note)], [Linked Master].[Power
Deposit $ to invoice], [Linked Master].[Chg Req1], [Linked Master].[Invoiced
Amount (Put Date in Note)1], [Linked Master].[Power Contract $ to
invoice], [Linked Master].[Chg Req2], [Linked Master].[Invoiced Amount
(Put Date in Note)2], [Linked Master].[Additional Power $ to invoice],
[Linked Master].[Chg Req3], [Linked Master].[Invoiced Amount (Put Date
in Note)3], [Linked Master].[Permit Flat Charge $ To Invoice], [Linked
Master].[Chg Req4], [Linked Master].[Invoiced Amount (Put Date in
Note)4], [Linked Master].[Encr Permit $ to invoice], [Linked Master].[Chg
Req5], [Linked Master].[Invoiced Amount (Put Date in Note)5], [Linked
Master].[Additional Encr Permit $ to invoice], [Linked Master].[Chg Req6],
[Linked Master].[Invoiced Amount (Put Date in Note)6], [Linked
Master].F55, [Linked Master].[Engineering Pay], [Linked Master].[On Time Card
Y or N], [Linked Master].[Engineer Permit Pay], [Linked Master].[On Time
Card Y or N1], [Linked Master].[Amount Paid to Engineer for Engr#], [Linked
Master].[Amount Paid to Engineer for Permit], [Linked Master].[Total Amount
to Pay Engr#], [Linked Master].[Total Amount Paid to Engr], [Linked
Master].[Pay Period End], [Linked Master].F65, [Linked Master].[Pre Fielded
Field Cxl'd Engineer $ ], [Linked Master].[Pre Fielded Field Cxl'd Engineer $
Invoiced], [Linked Master].[Power Deposit $], [Linked Master].[Power Mark Up
$], [Linked Master].[Power Deposit $ Invoiced], [Linked Master].[Power
Contract $], [Linked Master].[power Contract Mark Up $], [Linked
Master].[Power Contract $ Invoiced], [Linked Master].[Additional Power $
Charges], [Linked Master].[Additional Power $ Charges Mark Up], [Linked
Master].[Additional Power $Charges Invoiced], [Linked Master].[Permit Flat
Rate $], [Linked Master].[Permit Flat Rate $ Invoiced], [Linked
Master].[Permit $ ], [Linked Master].[Permit Mark Up $], [Linked
Master].[Permit $ Invoiced], [Linked Master].[Additional Permit $ ], [Linked
Master].[Additional Permit Mark Up $], [Linked Master].[Additional Permit $
Invoiced], [Linked Master].F85, [Linked Master].F86, [Linked Master].F87,
[Linked Master].F88, [Linked Master].F89, [Linked Master].F90
FROM [Linked Master]
WHERE ((([Linked Master].[Date Received Encr Pemit]) Between [Beginning
Date] And [Ending Date]));