Having trouble thinking of a way to approach this (to do with importing access data)

[James.90]

Edit:I just realised i had accidently writted the title as (to do with importing access data) it should read (to do with importing excel data)

This is going to be a trick hard to understand question but I will try my best to explain it

I have a database set out in the following way

http://img524.imageshack.us/img524/1350/databasetableli1.png

The way it works is;

Let's pretend Access Programmers is a company and working on different forums is a different job

So on one record it would read

James.90| Access Programmers|Tables Forum| Wed=3= Mon=2

Then the record below might read

James.90| Access programmers | Forms Forum| mon=5 tue=6

So each record is one unique company,Project and CTR which the person has worked for that week meaning if you only work on one forum you would only write one record out each week

Now the data i am receiving is in an excel file where it's set out in a daily basis

Where

One Day Date|Name|Company|CTR|etc

So if a person works 5 days a week on 2 companies each day that is 10 records when it should only be 2 records

So to sum it up. My database is set out weekly and the excel data is set out daily

My question

What would be the best way to convert this data into the database. Changing the database structure around is not an option and i can't change the format we recieve the excel data in. I can change it once i have the file thorough a converter but i can't change the raw source of the data

What would be a way to solve this problem because i am completly stummted and am open to any option of converting or anything

Thankyou for your time. Also if you have trouble understanding what i mean Please say so and i will upload a copy of the database and a copy of the excel sheet!

 

[gemma-the-husky]

import the table into access (some solutions would link to the excel data directly)

do a totals query, to add all the time spent on common projects - that reduces your 10 rows of excel data to 2 rows, because you are ignoring the date/time column effectively.

now you can process the 2 rows of data as you require

 

[James.90]

Hey thanks for the reply it's given me a good starting point

The main barrier which i can't seem to understand is this

In database one record has one single week ending date (e.g 29/02/2008)

And it has the fields saturday sunday all the way to friday.

The problem i am having trouble getting my head around is. How would i get access to decide what field to put the data in.

Let's say someone works 5 hours on the 28/02/2008 it needs to be able to realise that it's in the week ending 29/02/2008 and stick the 5 hours in the thursday colloum. That's the main thing i am having trouble understanding now

Thankyou in advance and please forgive the lack of posting sooner. I've been really busy!

 

[DJNickWatt]

I would import the excel spreadsheet as gemma suggested. Linking to excel files to read data can be slow. A simply DoCmd.TransferSpreadsheet should suffice.

 

[DJNickWatt]

Format("29/02/2008","ddd") will return "Thu" so if you're using a recordset against the table for example

oRS(Format("29/02/2008","ddd")) = 5

would put 5 into the column name "Thu"

 

[James.90]

Okay. Here is my method so far.

Made a new table. Imported the data into the new table from excel

Now this is the part where i am getting lost. Would i make a query to search for duplicate values with name, company, projet number etc. Or a form.

I will upload a copy of the database and excel spreadsheet with sample data if anyone could have a look at i would appriciate it

And thanks to the people who repplied so far!

 

[boblarson]

Seems to me as though the spreadsheet is properly normalized and you are trying to un-normalize it. Keeping the data as it comes to you with one record per day per project will be significantly more flexible and easier to work with when trying to create reports.

Here, Here! I concur with that.

 

[James.90]

Edit:

I discussed it with my boss and we are going to have a seperate database with the new system and one for the old one just temporary

My main question now is this

How would you write a query to do the following

Bascially i want it to check if a record falls between 2 dates, has the same data such as company, ctr, project etc it will add the hours to make a week ending hours

Just like to say i am not asking someone to do it for me but would just like to know the best way to approach it. I was thinking of using a check duplicate data query and then changing it to check between 2 dates and add the hours up

Another question. Can a query add values up or would i have to make a form.

In my old system i just had a query that would grab the records and display them as a report. What would i need to do to set this up

Thankyou everyone who has replied so far. I am very grateful

 

[James.90]

Anyone got any ideas?