473,287 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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
3 1268
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Bill | last post by:
I have a book cataloge where I have an author id linked up to the book. However, I'm finding that some books have mulitple authors, and I'm wondering if there is a way to add more than one id to...
7
by: Marco Simone | last post by:
Hi, What is your opinion about using Lookup field in table. I would like to use lookup field in table 1, so that I can choose data in combo box from table 2. Is this good design of database? ...
4
by: Abbey Krystowiak | last post by:
Does anyone know if I can have a field in a row where I can add two lines of info without adding a whole new line? and then there would be a drop down menu? *** Sent via Developersdex...
1
by: Jim | last post by:
I have a form which is based on a join query. The join relationship is one to many. When a new record is added and the user enters a value for one of the fields in the "many" records, I attempt...
1
by: K. Davis | last post by:
I need to increment the maximum value of a field of a table by 1 when a form opens a blank record. (e.g. =max(!![trip_number}) so the logic and references are working at the form level. I've...
3
by: Chris | last post by:
Before I started to create table, etc to track unique form field record number assigments I thought I'd check to see if there is now a better way to do this in .NET. I have a parent form (table)...
2
by: Howard | last post by:
how would you design a table that allows an item to belong to multiple parent categories. single parent example: select * from table1 where parentid = 5 in this case parentid is an indexable...
4
by: Don Do | last post by:
Help I built a form/subform/subsubform setup using the access forms wizard. I have a table1 = parent, table2 = child, table3 = (grandchild?). There will be multiple records in table2 that tie...
33
by: mjvm | last post by:
HI, I have a main table (tblStudents) that holds students where the unique field is StudentID; and a related table (tblNotes) on a one-to-many relationship, where the unique field is NotesID, and...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.