Uploading data from excel

[optimus_1]

Morning.

I was wondering if anyone could possibly assist me.

I am trying to upload data from Excel into Access tables. I have successfully uploaded all the data apart from the Yes/No field. I have tried putting Y/N and Yes/No and neither is correct.

Is this possible to do and if so what format does it have to be in.

Regards
Nathan

 

[neileg]

Yes is stored in Access as -1 and No as 0.

 

[optimus_1]

Thanks for the reply, I have tried that too and with no success. I have all the columns sorted and with the correct Cell format but for some reason i just cant upload the y/n fields!.

Thanks again

 

[optimus_1]

Attached is a sample database and a sample of the data i am trying to upload.

I have tried reformatting it etc and cannot get it to upload. Is it possible and if so how do i go about it.

Regards
Nathan

 

[optimus_1]

here is the database

 

[neileg]

There's something odd about your spreadsheet. If I set up a fresh sheet with the same data and set the Yes/no field to 0/-1, it imports fine.

Having said that:
a) Your design is not normalised. You should not have a daisy chain of days of the week.
b) You don't need to import the Nil report field anyway. You can caculate it in a query. If the daily toals add up to zero, it's a nil return.

 

[optimus_1]

Thankyou neileg

I have managed to successfully upload the data thanks.

By daisy chain do you mean MON,TUE,WED etc..... I needed to report the information daily so this was the way i believed to be correct!. HOw would you of recommended this be done?.

Thanks again
Nathan

 

[neileg]

In a spreadsheet, you generally speaking store, display and manipulate data all in the same place.

In a relational database like Access, you store data in tables, manipulate with queries or VBA and input or display in forms and reports.

The table design needs to be the most efficient and need not resemble the way data is displayed. This process is known as normalisation. You only need an entry for one of your days if there is data to store. This would be done by having a related table for the daily figures. If you have data for Tuesday, you create a record, if you don't, you don't. A simple totals query will be able to return the weekly total, instead of summing Mon + Tues + Wed, etc.