Create fileds and populate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help please

I have a table with approx 200,000 records, I need to add 2 fields to the
table and populate them with data from another table. However the other table
only has approx 40,000 records. I really do not want to manually key in
200,00 records if it can be avoided.
The reason for the difference in numbers of records is because the first
table has been split into eigths of a mile, it's not as straightforward as it
sounds though, there are different lengths of miles i.e. 2 miles will result
in 16 records 2.5 miles will result in 20 records etc

Best regards
 
Jeff, thanks for replying

It's not an easy one to explain but I will try:

I have 7 fields in a table currently and I need to expand it to 9.
The field I need to populate is a milage start and end point split into
eighths of a mile.
The problem I have is the data in the table I need to import from is not
split into eighths, it only has the start and end point, hence the reason for
the difference in the number of records.
I think Ideally I need to split the table I'm exporting from into eighths
then copy the field into my new table, but I'm not sure how to do this.

Hopefully you can help.

Best Regards

Les
 
Les

I'm trying to imagine a database table "split into eighths-of-a-mile". This
sounds more like a spreadsheet to me than a database.

Take a step back from the actual structure you already have, and see if you
can describe the underlying entities (the things about which you want to
keep information) and the relationships (how entities are related -- for
example, one "Order" can have multiple "Order Details"). Since Access is a
relational database, you will only get good use of Access functions and
features if your data is well-normalized.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Jeff

You're probably right, Excel might have been better for my purposes, but
with 200,000 records I didn't have a choice. I know Excel 2007 would handle
it but we haven't invested in that yet.

When my table is complete I will probably do the analysis I need to do with
a pivot table linked to it.

I will try to explain further.
Both tables have the fields 'miles from' and 'miles to', the first table
will have 1 record whereas the second table could have several records - 1
for each 1/8 of a mile. The first table contains a field called 'category',
this is the field I need to populate in the second table but for each 1/8 of
a mile rather than each length of mileage.

Hope this helps.

Les.
 
Well, part of the problem here is that I can't "imagine" the tables in my
mind.

However, your first tid-bit is the miles from/miles to.

Are these fields the SAME values in both tables?

if yes, then you can do a relational join on both tables. Simply fire up the
query builder, and drop in the first table
(I assume you already crated the two fields you need into this table).

Now, just drop in the 2nd table....draw join lines from the first table to
the 2nd table (on the miles from, and miles to).

You can now switch the query builder into update mode..and update the two
fields to the value (not clear what values from the 40,000 record table you
want????

You mention that up to 8 records may be present for the smaller table, but
then mention that only TWO new fields need to be added to the 2nd
table..which of the 8 possible records values do you want to place in the
possible two new fields?

From the sounds of the above, you can likely use a update query, but you
have to clarify which of the 8 records fields goes into the two records.

(however, the above join idea above should get you started).
 
Back
Top