I am new to databases but I have been reading here and there...I have a few questions:
1-I ran the import excel wizard which handle my 2d table quite nicely but I am wondering how to break out a table from the main one after the wizard has already run through - I was able to do it pretty easily during the wizard but I think a couple more columns could be broken out into their own tables...no idea how to do this
2-a yes/no boolean - should it be identified as a yes/no or should I leave it as text? I am getting 0s and -1s...
3-is it wrong to use multiple entries in a table where each each entry would typically only have a one to one relationship?
4- this may demand its own post, but I am wondering if anyone has seen any templates for architectural space databases...I haven't been able to find anything, but there must be something like it out there...
and back to issue no.1 - "1-I ran the import excel wizard which handle my 2d table quite nicely but I am wondering how to break out a table from the main one after the wizard has already run through - I was able to do it pretty easily during the wizard but I think a couple more columns could be broken out into their own tables...no idea how to do this"
you replied that I should use a make table query...couldnt figure this one out. I was able to make the table, but it wasn't linked back to the root or master table that essentially is keeping track of how all the entries are connected...
Perhaps not "invalid" but it is clearly not the best practice.
Issue no 1. Make a query to extract out the fields you want to "break out" in to another table. Then select the Make Table query type and run it. This will create a new table that contains what your query would normally show.
ok, thanks...I was able to make the table as you instructed previously, but then I found that the entries from the original table were still listed as though they remained in that table - instead of being called in the column heading as "Lookup in <insert table name here>" - this leads me to believe that I now have those entries in two spots without the linking that I was after...I don't know if you can understand my commoner's attempts to describe what is happening...
That's what it is designed to do TBH, that is what I thought you wanted to do ^^
Once you have the extra table, you can then start looking at:
* Creating an ID column for the new table.
* Removing redundancies from the new table.
* Adding the foreign key in the main table.
* Creating a relationship between the two tables.
* Verifying that what is done is correct and complete.
* Deleting redundant fields from the main table.
That's what I would be doing.
There is no easy way to move out and relate such data fragments... actually there is one thing, the table analyser (Tools - Analyse - Table), but I have never found it to be terribly accurate. Give it a go but for your sake, do it on a copy of the database and make sure you have adequate backups!!
now I have moved the animalID table out to its own table separate from the one that starts to bring their IDs together with their attributes. I am making a table that is a predatorPrey table - one comlumn predator, one side prey. I looked a little more into the multiple entries issue and it was as I suspected, a special MS feature that does all the breakdown into individual rows behind the scenes.
I have lookup fields for both predator and prey and they refer to the animalID table. I am trying to get the lookup table to restrict the values to those who are attributed as carnivore or omnivoreim in a complete different table. I tried to use criteria in the Design view of the Query but when I went to make the table I got unexpected results.
so just to brak it down - there are a number of tables that try to reduce the number of entries, eg a table dedicated to the animals identified by their names, a table for diets, a table for specialization, one for tables etc etc.
then I have a table - let's call it Master - which connects these disparate tables composed of lists of attributes.
Then I want a table which is constructed from a query of the Master table with a "criteria" imposed on it which restricts the values - in this case "carnivore" AND "omnivore" for predators.
Yes it's possible. Using queries to restrict records shown is very normal.
If you could post up your table structure, you could get more specific advice to address issues like a query giving you "unexpected results" ... of course you'd need to elaborate on what this means ... in what way were the results unexpected?