Info from different sources-Suggestions needed

[nancier]

Hello,
I'm looking for a better approach to this problem and would welcome any guidance.

I work with different data each week, the data contains mostly similar fields though the field names are always different, ie. Invoice-Date versus Inv-Date. Some data includes a Freight column while other data does not, etc.

Here is an example of everything I'm provided with.

Company Info:
Exam # = 34
CurrDate = 02/03/2005
ID = 9878
Exam Type = 554
Reason-Q = From Run
Tax-Code = 24
Loc-Code = 0000-00000-0000
Reason-A = Tax Paid
Det-Avg = D
T-Rate = 7%
Comments = None

Data Layout
Invoice-Date
Vendor Name
Description
Amount
Inv-Num
Freight
Sundry
Service

I need to create a query that generates a file that must be in the format below based on the Company Info and the Data Layout. In this example, I would want to bring in Freight as Num-1, Sundry as Num-2 and Service as Num-3. Also, again please notice that the Field Names in the Export Layout are different than the Data Layout Name, ie. INV-DATE versus Invoice-Date. Thanks

Export Data Layout
Field Name
EXAM#
SYS-DATE
AUD-ID
EXAM-TYPE
INV-DATE
INVOICE-#
NAME
DESC
REASON-Q
TAX-CODE
LOC-CODE
Amount
DET-AVG
T-RATE
CHAR-1
CHAR-2
CHAR-3
CHAR-4
CHAR-5
NUM-1
NUM-2
NUM-3
NUM-4
NUM-5

 

[AppStaff]

Sorry but its not clear to me what the challenge is for you. From your post I gathered that the names being different in the Input and Output is a source of some disconcern. Are you asking how to create a query that can produce a file? Are you asking how to create a query that has different names from the source? Perhaps you aren't sure how to import the file? You should probably specificy also what type of file you'd like to create. Perhaps a table is sufficient?

 

[nancier]

Sorry for the confusion. I am trying to create a table to be exported based on the Data file and the other information I have. But it has to be in the exact export data layout that I specified in the first post. And you are right that the names being different in the Input and Output is a source of some disconcern.

Thanks

 

[GingerR]

I have the same question as AppStaff: what kind of file are you importing/exporting? Access db table? SQL Server table? txt file? csv? Excel? If all you need is to rename fields, I don't see why you're importing it into Access?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AppStaff]

Don't worry about the names. Here's what I suggest. Search your help files on these topics if your unfamiliar with them.

1. Attach your input file as a table in your database. File, Get external data, link

2. Create a new table in access with the exact specifications and names you want. However, be sure not to be so restrictive in your definition as to not allow new information to be brought in from your attached table(s).

3. create an append query that takes info from your attached table and appends it to the new table you designed in ms access. This is your x-reference if you will. this is where you specify what field from the import goes into which field in your output. You will see what i mean as you look into it.

4. Exporting may be done in a number of ways. You should be able to search help and select the method that best applies.

Don't worry, nothing here is an advanced step and if you struggle on a specific task let us know. HTH