Table Design View

[Heather]

Hi, I am trying to create a table that I importing Excel data into and the
first couple of columns I need to come thru a certain way

Booking Qtr should look like YYYY-1, YYYY-2, YYYY-3, YYYY-4

and Year should look like YYYY

is there something I can put in the Input mask for them to come thru like
this?

Thank you

 

[Steve]

You don't want to do it that way! Create a field in your table for year and
a field for quarter. (Both fields Text) Then use a query where you need
Booking Qtr and use a calculated field in the query to get the format you
want:
BookingQtr = BookingYear & "-" & Qtr

Steve
(e-mail address removed)

 

[Klatuu]

What you don't say is what the data looks like coming form Excel. We need to
know that before we can show you how to format it. BTW, Input Masks are only
for form controls, not for queries.

 

[Dale Fye]

Dave,

Any idea when/if the Access team is going to allow us do define and save our
own import specifications for Excel files? They do it for other file types,
why not Excel?

This has been one of my pet peeves for a number of years now.

 

[Klatuu]

Dale,

Haven't heard a word on that. I find it irrating as well. What I usually
do is use a special table for an import that has the same data types as what
is coming from Excel, then use append queries to reformat the data into
production tables.

Talking to the Access Development team is interesting. At the MVP Global
Summit in April, I had a chance to meet with them. My question was why does
a subform load ahead of the main form. The answer was "It has always been
that way". Problem is no member of the team has been there long enough to
know why.

 

[Dale Fye]

That's another of my pet peeves. The way I work around that is that I
usually save the form with the subforms SourceObject empty. Then, I'll put
some code somewhere (Load, Current, Timer) that sets the source object based
on the status of the form.

 

[Heather]

Thank you all for your comments & help Here's what I am trying to import
into Access - I'll put an example of what it might look like in paratheses)

Booking Qtr (2006-2)
Year (2006)
DealID (6800000)
Deal Type (New)
Sold-to Name (Customer A)
Contract End Date (12/29/2007)
Renewal Year (2008)
Renewal Qtr (2008-4)

and then there's a bunch of $ fields and calculations following

Thank you

 

[Dale Fye]

Heather,

I think I would do as Dave recommended.

Create a table that contains the structure of the data you want to import
B_Qtr (Text, 9)
Year (Numeric, Long)
DealID(Numeric, Long)
Don't include Deal Type in this table
Sold_To_Name (Text, 45)
Contract_End_Date (Date/Time)
Renewal_Year (Numeric, Long)
R_Qtr (Text, 9)
Field1 (Currency)
Field2 (Currency)
Field3 (Currency)

In your code, do the following:

1. Run a delete query that deletes all of the data from this table
2. Use the Transferspreadsheet method to append the data from your
spreadsheet into this table
3. Use an append query to migrate this data from your import table into the
production table. In the production table include your Booking_Qtr and
Renewal_Qtr fields (both of which should be integer data type) , but when
you run the append query, strip the quarter off the back of the year using
the Mid( ) function. It will look something like:

Mid([B_Qtr], 6)
Mid([R_Qtr], 6)

Once you have these imported into your production table, you can concatenate
them with the Booking year and Renewal year to display it in a format,
something like: [Booking_Year] & "-" & [Booking_Qtr]

BTW, I strongly recommend that you remove all of the spaces from your field
names. Spaces in table and field names force you to wrap any reference to
them in brackets [ ] which can get a bit annoying after a while. If you
feel you need space between words in a table or field name, use an
underscore.

Dale

 

[BruceM]

I'll have to try that the next time it gives me fits. Always seemed pretty
silly to start with the subform. It's more so now that I hear the only
reason is tradition.