Access and SQL question

I am currently trying to import data into an access db from flat files and excel. The problem i'm having is the flat files have the data in 3-6 colums, but one colum is a task colum, this had different entrys for each row depending on what task was done. it has a time stamp, and an order number. the only thing unique is the time stamp, and the task. the order can be repeated because multiple tasks can be completed for the same order.
like this.


06/01/2003 10:55:56 AM 232689 AF_OCD
06/01/2003 10:55:57 AM 232689 AF_CONF
06/01/2003 10:55:57 AM 232689 ASI_FOC
06/01/2003 10:57:08 AM 232689 NEWTASK

I'm trying to figure out how i can transferr this data into a table and have the coding move the data by task but keep the row info in place. and place it into the correct tables in access. I dont think access has the ability to do this on its own (its only the 97 version) but I would like to try to do this with SQL.

Any ideas?

 

you can modify the importing of the file to include fields for the sections. when you import it, the wizard will ask if you want to assign a primary key automatically or allow you to do it manually. Choose automatically and it will create an AUTONUMBER field called ID for that table. So you can use that as your unique identifier.

what's the delimeter? is it a space? or a comma? You can choose what character delimits the data.

 

In the flat files it is a comma. I can get the data to import in the format it is in. But I was wondering if I can get it to seperate the data rows by the task type and then insert it into different fields in a seperate table???

 

I'm not sure I follow you.. but I think what you are wanting to do is this..

import the data to one table. Then import it again and select DO NOT IMPORT on the other fields except the task type and import that to a table of it's own. From here you'll need to create a lookup for the first data tables field for task type and point it to the other task type table that you just created.

If you're in the USA and are having problems, give me your phone number and I'll walk you through it.

 

Basically I'm trying to sort one table by row, sorting on the task. then input the results into another table by order number where the dates for that task are populated? make sense?

 

so two tables.. one with all the info sorted by task and one that holds data ONLY If the date is populated? is this right?

 

Kinda, The main table is set up with the Primary Key as the order number. this table has all the tasks in it and the dates they are completed. the other flat file that I need to import has the data sorted as the task as the primary Key but only if the task and the order number are not the same.

 

why not create a one to many relationship between the table and the imported data table and query on both of them using a join query?

 

I'm trying that now, but I'm having problems getting the Join function to work in the access version of SQL.

 

make it easy on yourself and use the design view ..add both of the tables. If no relation exists, then make one by selecting the primary key in table one and click drag to the foreign key of table 2 .. there's your join. Then just add the fields that you want to query for.

 

how did you make out?

 

I got the data to import. But Now I'm having a bigger problem.

I Have 2 tables that contain data. the common field in each table is the order number field. I can run a query that will go through each table and select the distinct order numbers for each. I know that the Bucket table has over 14000 distinct enrties in it. where as the ASI table has only 4800, But when I try to run a comparison that will give me all entries in the Bucket table that are NOT in the ASI table I get NOTHIING

However if I run a report that is the poopsite, one that asks to give me all the orders that are equal I get a valid report.

The script that I am running to find all the distinct entries on the bucket is this

SELECT DISTINCT [Bucket Task Prior Dates table].[Serv Req Order Number]
FROM [Bucket Task Prior Dates table];

This gives me back over 14000 entries.

When I compair it to the other database to give me all entries that are in both tables ...

SELECT DISTINCT [ASI data list].[Serv Req Order Number], [Bucket Task Prior Dates table].[Serv Req Order Number]
FROM [ASI data list] INNER JOIN [Bucket Task Prior Dates table] ON [ASI data list].[Serv Req Order Number] = [Bucket Task Prior Dates table].[Serv Req Order Number];

This gives me arround 4800 like entries.

Now when I try to query the Bucket table to show all entries that are not in the ASI table I get nothing

SELECT [Bucket Task Prior Dates table].[Serv Req Order Number]
FROM [ASI data list] INNER JOIN [Bucket Task Prior Dates table] ON [ASI data list].[Serv Req Order Number] = [Bucket Task Prior Dates table].[Serv Req Order Number]
WHERE (([Bucket Task Prior Dates table]![Serv Req Order Number]<>[ASI data list]![Serv Req Order Number]));


I need a drink



Any help would be greatly appreciated.....

 

AAAAAAAAAAAAAAAAAAAAAHHHHHHHHHHHHHHHHH!!!!!!!!!!



I need a gun. they just changed the report format and now all the updates are not working.....


I have a JOIN error now


I need a beer

 

OK I fixed the JOIN error, but the other is driving me nuts

(I know it's a short drive)

 

you are using the wrong join type..

inner joins get only records that are equal.

left join will give you all the records in tableA and and the ones that have a relation in tableB.

right join will do the opposite of a left join..

you might want to do something like this..

select tableA.* from tableA left join tableB on tableA.id=tableB.id where (TableB.FieldNameHere) Is Null

 

I'll let you know how it goes, thanks for the input

 

That did it, thanks for the help.

I'll buy you a beer or a dozen

 

I'm medically allergic to alcohol, so how about you buy me some RAM instead