如何阻止Access在导入时自动对数据进行排序？

I am importing an Excel spreadsheet into Access 2010. I created a Saved Import that will import a column of data intentionally out of order. The query I created needs to take the exact order of this data, return a value associated with it from a master table in our company's DB, then allow me to export both of these fields to Excel. I need to do this because I need to copy and paste the export on top of the values of another spreadsheet.

我正在将Excel电子表格导入Access 2010.我创建了一个Saved Import,它会故意无序导入一列数据。我创建的查询需要获取此数据的确切顺序,从我们公司的数据库中的主表返回与之关联的值,然后允许我将这两个字段导出到Excel。我需要这样做,因为我需要将导出复制并粘贴到另一个电子表格的值之上。

The problem is that when I import into Access the FG column is sorted by A-Z. This can be seen in both the table import and the results of the query. How do I keep my data in the mixed up order throughout the whole process?

问题是当我导入Access时,FG列按A-Z排序。这可以在表导入和查询结果中看到。如何在整个过程中将数据保持在混合顺序中?

Prepared Import sheet

准备进口表

FG
D
B
E
A
C

After importing

FG
A
B
C
D
E

Query

FG         Description
A          descript of A
B          descript of B
C          descript of C
D          descript of D
E          descript of E

2 个解决方案

#1

To solve this problem, I had to use the option "Let Access create a primary key for me" when importing. This allowed the table to be populated with the data in the same order as was on the import spreadsheet. To make sure the query also keeps this same order, I had to include the new "ID" field as part of the results.

为了解决这个问题,我必须在导入时使用选项“让Access为我创建一个主键”。这样就可以使用与导入电子表格中相同的顺序填充表格。为了确保查询也保持相同的顺序,我必须在结果中包含新的“ID”字段。

Final SQL Code

最终的SQL代码

SELECT FGImport.ID, FGImport.FG, dbo_Active_Part_Number_List_Syteline.description
FROM dbo_Active_Part_Number_List_Syteline RIGHT JOIN FGImport ON dbo_Active_Part_Number_List_Syteline.item = FGImport.FG
GROUP BY FGImport.ID, FGImport.FG, dbo_Active_Part_Number_List_Syteline.description;

#2

This may be an OLD post, but I would suggest opening Excel and creating a simple macro to enumerate the records before importing to Access; once you've imported the file, this enumartion (we'll call it "MacroField") will be part of the data set you import (We'll call this table "Table1"). You'll still retain the records original formatting by querying the record and ordering by Table1.MacroField asc/desc. There has to be a better way of doing it, but if you're in a pinch this does the trick.

这可能是一个旧帖子,但我建议打开Excel并创建一个简单的宏来枚举记录,然后再导入Access;一旦你导入了文件,这个enumartion(我们称之为“MacroField”)将成为你导入的数据集的一部分(我们将这个表称为“Table1”)。您仍将通过查询记录并按Table1.MacroField asc / desc排序来保留记录的原始格式。必须有一个更好的方法来做到这一点,但如果你处在紧要关头,那就行了。

Sub liminal()

For i = 1 To ThisWorkbook.Sheets.Count

对于i = 1到ThisWorkbook.Sheets.Count

If Sheets(i).Name <> "PrimaryTab" Then
    ct = 0
    Sheets(i).Activate
    Range("B1000000").Select
    Selection.End(xlUp).Select
    en = ActiveCell.Row

        For p = 1 To en
            ct = ct + 1
            Sheets(i).Range("A" & p).Value = ct
        Next p

End If

Next i Sheets("PrimaryTab").Range("A1").Select MsgBox "Data Set, please open and load to MS ACCDB" End Sub

下一页i Sheets(“PrimaryTab”)。范围(“A1”)。选择MsgBox“数据集,请打开并加载到MS ACCDB”End Sub

#1