Recent content by nathanstevenson

I am in a similar position as you are - the DB I have just become responsible for has 16.5 mil records just in the one table, it's 21gigs, but that one table alone is 2.4gigs. It's at this point that you have to look very closely at your stored procedures, particularly the joins you are using...

Hi Guys, I have met with a small problem in this process, and I am not sure why it is arising. Everything is working smoothly except when I try and join on more than 2 tables. Say table no 4 has to include foreign keys for tables 1,2 and 3. What happens is that I get all the keys for tables 1...

I see the light!! Beetee, your example was crystal clear and Dan, what can I say except classic. Bring on the smiley wars. I especially liked: Now, the child table becomes a parent for another table and the process is repeated (like in real life :)).

Hi beetee, It appears your advice has been clouded by my confusion :) For clarity: Is a new make-table query necessary for each set of table data coming from the flat file? And: Are all the new tables linked via the relationship with the flatfile ID? Also in response to Danvlas, Can you...

I am starting to see the light.. I have been searching for this answer for a very long time. Just to check.. say the flatfile contains data for 8 tables. Do I create a query for each table with all the fields that need to be unique, but include the Primary ID from the flatfile table (so that it...

Hi Beetee, Good reasoning there, only thing is that the 5 fields are not unique, so this algorithm is used to create 1 unique field based on 5 different fields (which can be similar within the same column). This unique key is then used in the manner in which you speak of above, along with other...

Hey SteveCarey, The problem is that somehow the column header F9 (i.e. column header 9) is missing. This causes the import process to stop, and it also slows things down a lot. Solution: Simply type in the appropraite column headin, and it will work. You also need to investigate why this...

If you import excel data into your database, sometimes Access goes to the end of the file and imports the limit of 65,535 lines, which can produce a rather large database. Simple solution to this is set a named range in Excel by Going to Insert->Name->Define and then setting your range. When you...

Hi there, Below is a routine to create unique IDs from a flat file, which enables direct importing into Access. The problem is that it takes a long time (approx 2 mins for 330 lines) to execute. Is there someone REALLY bright out there that can help me speed this up? I don't know much about...

Hey Thoey, Okay, I have been mucking about with it, but isn't an update actually INSERT INTO? I had: INSERT INTO [Monthly Import].[Manager ID] FROM Manager.[Manager ID] WHERE Manager.[Manager Name] = Manager.[Manager Name] AND ... AND ... Except it doesn't seem to work, I keep on getting...

Hi there, I have 2 tables, I want to find the matching records (based on 5 different fields) and then update the first table with the ID from the second WHERE the records match (i.e. into a foreign key field). Any advice? Nathan

Hi Beetee, Okay, I got step 1 no problem. Appended unique manager records from table "Import" to table "Manager" which contains an autonum field for Manager ID. Step 2 is the part I am having conceptual difficulties with. Do I make an append query, with tables Import and...

Hi AccessAce, Some feedback first - your advice worked brilliantly! Just coming back to this from a different perspective now, as I am testing this in an autonum DB, to try and make it easier to import on a regular basis. You said with 9 tables, the 10th one with all the keys would be the...

Hi, I have built a database with autonumbers, to test whether or not it is better to use them. I have all my data in one big table with 28 different columns and 280 lines (for testing purposes). I want to split it up and distribute it into 8 different tables. How do I maintain relationships if...

Hi! I am trying to create a query that shows all unique records in a staging table based on "x" no of fields. This is for importing so I need this list of unique fields which is a subset of the staging table. Then I need to compare this list to the destination table and only add...