By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Can/should Status field be on Parent table in 1 to many Relationship?

P: n/a
Hi -

I have a little system in Access 2003 to enter and track Problems -
Help Desk type situation.

I have a Main table tblTickets and a Child table tblTicketActions

tblTickets has a TicketKey (autonumber) and tlbTicketActions has that
field to connect the two tables. (It also has its own autonumber
key).

I have a status - TickeyStatusKey (tlkpTicketStatus) on the Actions
table. The user chooses the Status after reporting whatever actions
have been taken (Opened, In Process, Closed, ReOpened).

I have to write reports that ask for all Closed Tickets (and their
detail) or all Open Tickets (and all their detail).

I'm finding this challenging, as I have to examine the
tblTicketActions to find the status.

My question is, Can I put the TicketStatusKey on the Main table
(tblTickets)? And then I'd have to update it every time there is an
action.

I don't know what is best to do. Insight is appreciated.

Thanks -
sara
Mar 14 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"sara" <sa*******@yahoo.comwrote
They query you wrote, how will it give me
the latest record if 2 actions were taken on
the same date? We do not require TIME be
entered for Action date, so doesn't the time
default to midnite in that case?
Unless you are writing down the actions and keying them in later, which
would seem to be a rather clumsy business approach, why do you require
either Action Date or Time to "be entered"? Why does it not default to
either "Date()" or "Now()"? The second of which will give you hours,
minutes, and seconds as well as the date, and you can leave it editable for
those crises when an action has to be entered "after the fact".

You should be able to use a TOP 1 subquery to retrieve the most recent
Action record, if there's a need. I'd quite likely use a Form in a Subform
to show all the Action history for the ticket, which might be helpful to the
person taking the call / modifying the information.

Larry Linson
Microsoft Office Access MVP
Mar 14 '08 #2

P: n/a
On Mar 14, 2:12*pm, "Larry Linson" <boun...@localhost.notwrote:
"sara" <saraqp...@yahoo.comwrote

*They query you wrote, how will it give me
*the latest record if 2 actions were taken on
*the same date? *We do not require TIME be
*entered for Action date, so doesn't the time
*default to midnite in that case?

Unless you are writing down the actions and keying them in later, which
would seem to be a rather clumsy business approach, why do you require
either Action Date or Time to "be entered"? *Why does it not default to
either "Date()" or "Now()"? *The second of which will give you hours,
minutes, and seconds as well as the date, and you can leave it editable for
those crises when an action has to be entered "after the fact".

You should be able to use a TOP 1 subquery to retrieve the most recent
Action record, if there's a need. *I'd quite likely use a Form in a Subform
to show all the Action history for the ticket, which might be helpful to the
person taking the call / modifying the information.

*Larry Linson
*Microsoft Office Access MVP
Thanks, Larry.

The GOOD news is that I actually did structure the Actions form as a
Form with a Subform showing all action history for the ticket.

The BAD news is that most actions are entered after the fact. Not
sure on the details, but it seems that 3 people perform the actions
and 2 of them call or email the third with actions. Before I wrote
this system, they tracked all by hand, and then Excel.
Further, they are often performing actions on multiple tickets and are
not able to get on the system to record their actions. (We are retail
- 18 offices/stores/warehouses and not connected to the (only) server
which is in the home office. Eons behind the times, but believe me, I
have made huge progress to even have PCs).

However, I am going to speak to the person who does most of the entry
and see if we can get them to enter the info themselves - as you
suggest. It will be a good start.

Not meaning to sound defensive, but it may help if you understand the
constraints of our situation a little bit.

sara
Mar 14 '08 #3

P: n/a
"sara" <sa*******@yahoo.comreplied in message
news:ef**********************************@x41g2000 hsb.googlegroups.com...
>
Done! It took me a while, but I finally got the report to work.
I am VERY appreciative of all the help.
Good news, Sara.

All the best.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Mar 20 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.