Export to Excel and Update status in Table and Unrelated question on autonumber

[Jenn]

I am not an Access person but I have been volunteered to help another
group with their database. They have a table/query/form which holds
data needing to be exported to Excel but they only want to Export new
entries. My thought is to add a new "STATUS" field to the table and set
it to "NEW" by default. On the form, when they click to export the
data, it should only export the rows with the status of "NEW" (I assume
this would be accomplished in the query) and then, after export, I
would like the STATUS to be updated to "SENT". Is this possible? Any
example code would be *greatly* appreciated.

Also, since I'm asking, this group is trying to use this database sort
of as a "ticket desk" concept. They'd like the autonumber field
generated in one table to become the unique id in related tables to use
sort of as a tracking number. There are about half a dozen tables
which will require the utilization of this autonumber value as the
requisition travels through the business process (utilizing different
tables along the way). Does anyone have any examples, direction on how
this can be accomplished? I'm not even sure how you can select the
value on subsequent tables easily as even if you wrote a record to each
table at the onset, you'd have to find that value (tab, dropdown, etc.)
in each table each time.

 

[Guest]

As to your first question.
I would recommend you make the Status field a Yes/No (boolean) field with a
default value of No (False). This saves space and is easier to deal with.
Then use a Select query for your export that is filtered on Status = False.
You don't have to export that field. You can unclick the Show check box in
the query builder. In the Criteria row for Status, just type in False. Now,
only new records will be exported.
Now you will need an update query to update the status field. You only need
to include the Status field in the query. No need to take time updating
fields that are already correct. Just include the one field. In the
Criteria row, enter False and in the Update To row enter True.

As to your question regarding using an Autonumber to relate the various
tables. This is a normal situation. Usually, you will have a table that
contains information regarding a primary entity. In your case, it is a
Ticket. All the unique information about a ticket should be in that table.
When you analyze your work flow and data requirements, you will find there
are cases where there may be repeating information about a ticket. For
example, in a Contacts database, a contact may have several phone numbers
(home, work, cell, pager, fax). Since not every contact will have all of
these and some may have more, the correct way to address this is another
table. That allows for no phones or as many phones as you want. In this
case, the Contact table is the Parent table and the Phone table is the Child
table - a traditional One to Many relationship.

Now, the trick is to keep the two associated so you will know which phone
numbers belong to which contact. This is where the Autonumber field comes
into play.
When you create a new record with an autonumer field, it is assigned a
number. Do not be concerned about this number. It is like underwear. You
need it, but you usually don't show it to everybody. It should never have
any significant value to your application. It is only for relating tables.
In a relational database, every table should have a Primary Key (PK). This
is the field that is unique to each record.

Now we need to create phone records. The phone table will also have a
primary key. Since it is not a law that Primary Keys be an autonumber field
and you will see posters who prefer not to use autonumber fields, you might
think, "Okay, I will use the Phone Number as the primary key." That's okay
until Bobby Jones and Sally Jones who share the same home phone are both
contacts or Fred and Mike who both work at Acme Explosives where there is
only one phone number are both contracts.

So, I would stick with the Autnumber. Now to relate a child and parent, we
use the autonumber field value of the parent and put it in a Numeric Long
Integer field in the child table. This is a Foriegn Key (FK). This action
usually happens during data entry. Normally, when you have Parent/Child data
entry, it is done with a form / subform configuration. The parent record is
entered and edited in the form and the child in the subform. For this, you
don't need to show the key fields or their values. IMHO, it is not even a
good idea to do so. The child's PK will automatically be assigned, so you
don't even need to think about that. If you set your subform control
properly using the Link Master Fields and Link Child Fields properties, that
will aslo be taken care of for you.

Hope this was not too long winded and that it did help.

 

[Jenn]

Thank you very much for the thorough response. I am always amazed by
the helpfulness of complete strangers. Thanks again for not only
explaining the how but the why.