Bagaimana cara menautkan tabel excel ke kueri akses?

Anda tidak perlu mengimpor tabel database Excel ke yang baru
Tabel akses untuk bekerja dengannya di database Access. Sebagai contoh,
misalkan Anda melacak nilai siswa dalam database Excel dan Anda menginginkannya
buat kueri Access yang akan mencantumkan siswa mana yang mendapat skor di atas 85% secara keseluruhan
tiga ujian. Ikuti langkah ini

1. Membuka
   Akses dan buat database kosong baru
2. Pergi ke
   Mengajukan. Dapatkan Data Eksternal. Tabel Tautan
3. Navigasi
   ke File Data Siswa di folder Excel Anda
4. Pilih
   Lembar Kerja
5. Klik Berikutnya dua kali
6. Klik
   Selesai

Tautan ke database Excel akan muncul di Access
jendela basis data di bawah Tabel. Anda dapat memilih tautan dan membuat kueri sesuka Anda
biasanya akan. Saat Anda menautkan ke tabel Excel, tabel Excel asli akan
menampilkan semua operasi Access. Anda dapat membuat kueri, formulir, dan laporan, dan
menambah atau menghapus data ke tabel Excel—sama seperti yang dapat Anda lakukan jika Anda telah mengimpor
sebagai tabel Access. Nanti, Anda bisa menggunakan kueri Buat Tabel untuk membuat
Akses tabel dari data Excel, jika Anda menginginkannya

Ketinggalan tip?

Lihat arsip Microsoft Access, dan dapatkan tips Access terbaru kami

Bantu pengguna meningkatkan produktivitas dengan mendaftar secara otomatis ke buletin Microsoft Office Suite gratis dari TechRepublic, yang menampilkan tips Word, Excel, dan Access, yang dikirimkan setiap hari Rabu

Hai teman-teman, saya mencoba menautkan dari excel ke kueri akses yang saya buat. Kueri yang saya buat di Access menggunakan tabel sql tertaut melalui odbc. Saat menggunakan alat impor data eksternal excel, saya dapat masuk dan melihat semua tabel dan kueri saya, namun saya hanya dapat menarik data ke excel yang tidak mereferensikan tabel tertaut saya. Saat mencoba menautkan ke salah satu tabel atau kueri tertaut saya menggunakan tabel tertaut, saya mendapatkan kesalahan koneksi. Apakah ada cara yang lebih baik untuk mencoba ini?

Terima kasih telah melihat

Di Excel saya pergi ke Sumber Data Lain dan memilih Dari Microsoft Query dan memilih kueri Access saya untuk diimpor ke Excel tetapi saya mendapatkan pesan kesalahan berikut. "Fungsi 'Hari Kerja' yang tidak ditentukan dalam ekspresi"

FYI - Saya memiliki fungsi Hari Kerja dan kode VBA fungsi Hari Kerja dalam dua modul terpisah (keduanya ditetapkan sebagai fungsi Publik). Saya tidak yakin apa masalahnya saat mencoba mengimpor kueri ke Excel. Berikut adalah kode VBA untuk fungsi Workdays

Kode

Option Compare Database
Option Explicit
Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function

kata TerryHogarth21

Seperti apa kueri akses Anda?

Sering kali hal ini terjadi pada saya juga dengan menyambungkan ke kueri di Access dari Excel- biasanya solusi yang saya lakukan hanyalah memasukkan hasil kueri tersebut ke dalam tabel, lalu ODBC melalui MS Query/MS Access di Excel (seperti yang Anda lakukan di atas

Seringkali lebih mudah bagi Excel untuk mengkueri tabel Access vs mengajukan kueri

Klik untuk meluaskan

Apa yang Anda lakukan pada dasarnya menanyakan kueri, menjadikan kueri Excel sebagai kueri Access untuk hasil daripada meletakkan perintah SQL dari kueri Access tersebut di bagian SQL dari Koneksi Anda sehingga Excel menanyakan Tabel untuk hasilnya. Saya berasumsi bahwa semua data akan diperbarui melalui tabel akses yang Anda tanyakan (dan kueri tidak akan berubah?) sehingga kami dapat melewati koneksi ke kueri di Excel dan langsung menuju ke tabel

Di Pita Excel (Dengan asumsi 2010/2013) buka Data -> Koneksi -> Pilih Koneksi Anda yang Anda atur -> Definisi. Dalam jenis perintah pastikan tertulis SQL dan kemudian di Teks Perintah, masukkan SQL Anda yang ada di Access Query (Salinan Kuartal Fiskal dan Waktu Siklus) - meskipun Anda mungkin harus bermain-main dengannya. Fungsi Publik mungkin tidak berfungsi karena nama fungsi tersebut mungkin tertukar dengan fungsi Excel sendiri karena Anda menamainya serupa

Jika ada yang saya tinggalkan dari fungsi publik tersebut, lakukan hal di atas untuk mengembalikan hasil kueri di Excel, lalu buat makro yang akan menyegarkan data dan juga memasukkan rumus untuk mendapatkan Quarters, Cycle Time, dan Row Anda. Maka yang perlu Anda lakukan hanyalah menjalankan makro ini kapan saja Anda ingin menarik data baru -> itu akan menjalankan SQL, menyegarkannya ke Excel lalu menambahkan 3 rumus Anda untuk mendapatkan Kuartal, Waktu Siklus, dan Baris

Anda secara teknis dapat menambahkan Quarters melalui SQL juga sesuatu seperti di bawah ini jika Anda benar-benar menginginkannya

Kode

 SELECT ID,Date_Field, Format(Date_Field,"q") as Quarter
FROM Table1;

kata TerryHogarth21

Apa yang Anda lakukan pada dasarnya menanyakan kueri, menjadikan kueri Excel sebagai kueri Access untuk hasil daripada meletakkan perintah SQL dari kueri Access tersebut di bagian SQL dari Koneksi Anda sehingga Excel menanyakan Tabel untuk hasilnya. Saya berasumsi bahwa semua data akan diperbarui melalui tabel akses yang Anda tanyakan (dan kueri tidak akan berubah?) sehingga kami dapat melewati koneksi ke kueri di Excel dan langsung menuju ke tabel

Di Pita Excel (Dengan asumsi 2010/2013) buka Data -> Koneksi -> Pilih Koneksi Anda yang Anda atur -> Definisi. Dalam jenis perintah pastikan tertulis SQL dan kemudian di Teks Perintah, masukkan SQL Anda yang ada di Access Query (Salinan Kuartal Fiskal dan Waktu Siklus) - meskipun Anda mungkin harus bermain-main dengannya. Fungsi Publik mungkin tidak berfungsi karena nama fungsi tersebut mungkin tertukar dengan fungsi Excel sendiri karena Anda menamainya serupa

Jika ada yang saya tinggalkan dari fungsi publik tersebut, lakukan hal di atas untuk mengembalikan hasil kueri di Excel, lalu buat makro yang akan menyegarkan data dan juga memasukkan rumus untuk mendapatkan Quarters, Cycle Time, dan Row Anda. Maka yang perlu Anda lakukan hanyalah menjalankan makro ini kapan saja Anda ingin menarik data baru -> itu akan menjalankan SQL, menyegarkannya ke Excel lalu menambahkan 3 rumus Anda untuk mendapatkan Kuartal, Waktu Siklus, dan Baris

Anda secara teknis dapat menambahkan Quarters melalui SQL juga sesuatu seperti di bawah ini jika Anda benar-benar menginginkannya

Kode

 SELECT ID,Date_Field, Format(Date_Field,"q") as Quarter
FROM Table1;

_

Klik untuk meluaskan

Oke saya berhasil -

Dalam Perintah SQL gunakan ini

Kode

SELECT *
FROM 01_FY2014_Procurement_Log;

Kode

 =ROUNDUP(MONTH([@[Date_of_Soliciation]])/3,0)

Kode

 =IF(OR(LEN([@[Date_of_Soliciation]])<1,LEN([@[Date_of_Award]])<1),"Missing Date",NETWORKDAYS([@[Date_of_Soliciation]],[@[Date_of_Award]]))

kata TerryHogarth21

Oke saya berhasil -

Dalam Perintah SQL gunakan ini

Kode

SELECT *
FROM 01_FY2014_Procurement_Log;

Anda tidak memiliki apa pun dalam klausa where Anda, pada dasarnya Anda mengembalikan semuanya - apakah ini dimaksudkan?

Dalam data Excel setelah kueri, Anda perlu menambahkan 2 rumus

Anda memiliki 22 bidang jadi

Di Sel W1 ketik "Quarter_Field", masukkan rumus ini di W2

Kode

 =ROUNDUP(MONTH([@[Date_of_Soliciation]])/3,0)

_

Di Sel X1 ketik "Work_Days_Field", masukkan rumus ini di X2

Kode

 =IF(OR(LEN([@[Date_of_Soliciation]])<1,LEN([@[Date_of_Award]])<1),"Missing Date",NETWORKDAYS([@[Date_of_Soliciation]],[@[Date_of_Award]]))

Saya tidak melihat poin dari rumus baris Anda di Access karena sama dengan ID, jadi saya tidak memasukkannya

Sekarang semuanya disegarkan secara dinamis karena rumus menjadi bagian dari penyegaran saat Anda mengklik di mana saja di excel yang ditautkan ke koneksi. Satu-satunya hal yang akan berubah adalah jika Anda mengubah struktur tabel itu untuk menambahkan lebih banyak bidang, maka rumus untuk Quarter_Field dan Work_Days_Field harus masuk ke kolom lain

Klik untuk meluaskan

Kode

=IFERROR(IF([@[Date_of_Award]]="","","Q" &LOOKUP(MONTH([@[Date_of_Award]]),{1,4,7,10},{2,3,4,1})),"")

Kode

 SELECT ID,Date_Field, Format(Date_Field,"q") as Quarter
FROM Table1;

Untuk sebagian besar saya suka bertahan dengan menjaga perintah sebagai SQL vs tabel karena lebih fleksibel jika saya perlu menambahkan klausa di mana atau kriteria lain

Semua kueri Anda lakukan adalah kueri seluruh tabel dan menambahkan 3 kolom melalui rumus sehingga itulah mengapa saya menanyakan sumber tabel vs kueri karena rumus dapat diduplikasi di Excel setelah kueri dibawa kembali

Cara lain yang saya lakukan adalah dengan mengotomatiskan ODBC alih-alih melakukannya secara manual seperti di bawah ini di Excel sebagai contoh. Anda hanya perlu variabel untuk mencari tahu di mana kueri berakhir dalam bentuk kolom dan secara dinamis menambahkan rumus untuk mendapatkan Kuartal, Baris, dan Hari Kerja yang mungkin dapat dilakukan di Excel. Saya akan memposting di Forum Excel untuk bantuan lebih lanjut tentang itu jika Anda perlu

Sekali lagi ini adalah saran karena menanyakan kueri di Access to Excel di mana kueri menggunakan Fungsi yang ditentukan pengguna mungkin tidak berfungsi sepanjang waktu saat Anda mengalaminya. Oleh karena itu mengapa jika tidak ada fungsi SQL yang berfungsi, mungkin lebih baik membawa data kembali ke Excel dan menjalankannya dengan rumus

Kode

 SELECT ID,Date_Field, Format(Date_Field,"q") as Quarter
FROM Table1;

Harus menghilangkan keterampilan SQL lama untuk beberapa hal ini

Dalam meneliti Anda dapat mengambil kueri pemilihan itu dan memanfaatkan Fungsi ROW_NUMBER() yang tidak ada di Access karena itulah mengapa Anda membuatnya melalui fungsi pengguna

Berdasarkan posting ini Anda seharusnya menggunakan fungsi TOP 1 di Access untuk mengembalikan ROW_NUMBER
sql - Nomor baris dalam hasil kueri menggunakan Microsoft Access - Stack Overflow

ROW_NUMBER() dalam T SQL
ROW_NUMBER (Transact-SQL)

Dalam menggunakan fungsi ROW_NUMBER dalam Perintah SQL di koneksi Excel to Access bekerja dengan baik

Kode

 SELECT ID,Date_Field, Format(Date_Field,"q") as Quarter
FROM Table1;

Bagaimana cara menautkan spreadsheet ke database Access?

Bagaimana cara mengintegrasikan Excel dan Access?

Bagaimana cara menautkan kueri SQL ke Excel?

Bisakah Access dan Excel bekerja sama?