importing data from one table to another

[Guest]

Hi, I am very new to access and am in need of a bit of help. I am currently
working on an inventory system for our computers, and have all the data
entered into Excel. I have imported the spreadsheet into Access, but I would
like to create individual tables for the different PC types we have i.e. 1
table for all PII 1 table for all PIII etc. Is there a way I can get Access
to pull this data from my spreadsheet or the new database I created? I would
like only the fields with a value to be imported into the new table but onto
a seperate line in the table. Is this possible or do I need to re-enter all
my data?

 

[Guest]

What is a PC type?
If all the fields for all PC types are the same, then you should not do
this. You should just have a field that identifies the type. When you only
want to see one type, you user form filtering, report filtering, and/or
queries to limit which type is included.

Don't worry about blank fields in the table unless you have an entire row of
blank fields and don't want that row in the table.

 

[Guest]

That does not seem like a good idea.
All the records (if they represent a single type of thing) should be in ONE
table. You then use queries to extract the records you want e.g.
SELECT * FROM tblComputers WHERE PCType = "PIII".

The only reason to separate into different tables would be if your
spreadsheet contained different things e.g. PCs, Printers, Monitors and
Scanners. Even then a case could be made for keeping it all in one table.

Dorian

 

[Guest]

Thank y'all for the prompt replies. My spreadsheet has columns labled
Teacher name, Room #, PII , PIII , P4 etc. the values are just numbers in
each cell. I wanted to be able to tell Access to look in column C for a
number value and if there was to import the number, Teacher Name and Room
number into a new table. Am I doing this all wrong?

 

[Guest]

What 'things' do you want this Access database to store?
You need to set up a table for each type of 'thing' e.g. Teacher, Room, PC
You then need to think about how the 'things' are related e.g. is there one
room per teacher, can teachers share a room, do any rooms have more than one
PC etc. etc.
I would then do your data manipulation in Excel, create one spreadsheet for
each thing you want to import.
Then import to each MS Access table from its own spreadsheet.

Dorian

 

[John Vinson]

Thank y'all for the prompt replies. My spreadsheet has columns labled
Teacher name, Room #, PII , PIII , P4 etc. the values are just numbers in
each cell. I wanted to be able to tell Access to look in column C for a
number value and if there was to import the number, Teacher Name and Room
number into a new table. Am I doing this all wrong?

Yes... because Access IS NOT A SPREADSHEET.

Spreadsheets have "column C". Relational tables don't. When you link
to an Excel spreadsheet, you have the option of using the column
headers as fieldnames; in this case you'll have fields named [Teacher
Name], [Room #], [PII], [PIII], etc.

You can use a Query with a criteiron of IS NOT NULL on the field
[PII], and make it an Append query to append the other fields into an
existing table (or even a new table, if you want to use a MakeTable
query; this should be a rare operation though).

John W. Vinson[MVP]