Create record in one table automatically based on data from another form? (1 Viewer)

Denise2020

Member
Local time
Today, 13:54
Joined
Mar 31, 2020
Messages
82
I am trying something and I am not sure if this is the "right" solution to my problem, or whether it is possible, so I am open to all suggestions!

I have a database that has a table that records incoming help requests from external sources. We can call this External Support. I created a form to record new external help requests. Sometimes these help requests cause internal jobs, and I am creating a separate table for the internal jobs. We can call this RFI (Requests for Information).

An example of this would be an external agency finds an object that they need to identify and they call us. That would be on the external task list. We identify the object but realize that we don't have good information on it and need to update our own records. This would be the RFI/internal task list. This is at the request of my boss, who wants them divided in this way, unless there is a better solution.

So my first question is, should this even be two separate tables, or should it be one table with an identifier of whether it is external or internal?

My second question is if it DOES go into two separate tables, would there be a way, via my form, to enter data from the New External Task ALSO into the Internal Task list by checking a box?

I apologize, as I type this, I realize how completely convoluted and confusing this sounds but I cannot seem to explain it better. Thank you in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:54
Joined
May 7, 2009
Messages
19,169
on my opinion, do it on separate table.
on your form add a a textbox for the RFI and a button to generate the RFI number.
are you a QC?
 

Pac-Man

Active member
Local time
Today, 17:54
Joined
Apr 14, 2020
Messages
408
So my first question is, should this even be two separate tables, or should it be one table with an identifier of whether it is external or internal?
In my opinion, you should create separate table for RFI.
My second question is if it DOES go into two separate tables, would there be a way, via my form, to enter data from the New External Task ALSO into the Internal Task list by checking a box?
You can define a code and call it in a macro which runs on update of tables. I'm not in front of PC so can't give exact name of that macro (that is specific to table events) which puts data into your RFI table.

Update: that macro is called data macro which runs. But as you stated that you have form then you might not need data macro. You define your code to either write data into RFI by checking status of your checkbox or by defining button which is when clicked writes data into RFI table.
 
Last edited:

Denise2020

Member
Local time
Today, 13:54
Joined
Mar 31, 2020
Messages
82
Thank you, as always, arnelgp.

I am not a QC. I actually am responsible for a large archive of information, among other things, and was given six excel sheets that contained basically just a list of the nearly 10,000 file archive. The excel sheets were cumbersome and horrible and I discovered databases and realized it would help me in my job immensely. However, with no external support, it was left to me to do it (actually other departments are coming to me now to do the same for them, as they are still working in excel sheets) so I am really trying to learn as I go.

I currently have a checkbox for the RFI, as well as a textbox. I am having trouble wrapping my head around how to take data from the form and send it to two separate tables. I will try an example:

Ext. Support Case#
Case Date
Title
Description
Originating agency
Person of Contact (POC)
Resources used
Steps taken to resolve
Answer date
RFI yes/no
RFI description

So everything but the last two items go to the Ext. Support Cases table (fStodarenden). Checking the RFI checkbox would trigger the Case Date, Title, Originating agency, POC and RFI description to another table? Is that redundant? Is there a better way to reference each other?
 

Denise2020

Member
Local time
Today, 13:54
Joined
Mar 31, 2020
Messages
82
In my opinion, you should create separate table for RFI.

You can define a code and call it in a macro which runs on update of tables. I'm not in front of PC so can't give exact name of that macro (that is specific to table events) which puts data into your RFI table.

That are my two cents.

Thank you! That is what I was thinking but I am having trouble figuring out if I am breaking normalisation by pulling some of the same data to two separate tables?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:54
Joined
May 7, 2009
Messages
19,169
if possible, try adding Autonumber to your tables.
they will come in handy when you need them.

supposing you already have the autonumber (ID, is a good name)

RFI (table)
SupportID (long, FK, will hold the ID of the "other table")
RFI Number (text)
RFI Date (date time)
RFI Description (other)
...
...
RFI Close Date
(other field for infos)

// create a Query that will Left Join your first table to the rfi table. and use the query as recordsource of your form.
 

Pac-Man

Active member
Local time
Today, 17:54
Joined
Apr 14, 2020
Messages
408
Thank you! That is what I was thinking but I am having trouble figuring out if I am breaking normalisation by pulling some of the same data to two separate tables?
You can use define relationships between external support table and RFI table if data is exactly same. You'll use only recordID in RFI table and add new fields specific to RFI.
 

Denise2020

Member
Local time
Today, 13:54
Joined
Mar 31, 2020
Messages
82
Oh! I should have added that but yes, all tables have an autonumber called ID.

I think I am wrapping my head around this now. Thank you both so much! This will give me a great start to work with. I am sure I will have more questions, and I really appreciate your help.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:54
Joined
May 7, 2009
Messages
19,169
i think you already got it.
sometimes we get blundered when we watch to close.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:54
Joined
Feb 19, 2002
Messages
42,970
Duplicating data in multiple tables is generally wrong. I would use a single table with an indicator of external or internal but it really depends on what you are trying to track. What happens to the original "external" record once you now decide it needs to be "internal"? Do they simply diverge and each is now treated separately? Or do you ever have to match them? And if you have to match them again, how would you know what data is correct?
 

Denise2020

Member
Local time
Today, 13:54
Joined
Mar 31, 2020
Messages
82
The original external job remains, as does the internal if there is one. So basically one call for support can result in either one job or two, if that isn't overly confusing? They should reference back to each other, but the RFI/Internal job, being strictly internal, doesn't have such a high priority when it comes to tracking the details. It is basically a second task someone has to fix up and then it is done (although the record is never deleted, it is just not all that interesting to us once it is done). The external record, however, is important to save and document so those records stay "active" even when complete, mostly for statistics but also if a question comes up later as to how we processed the support request and what information we provided.
 

isladogs

MVP / VIP
Local time
Today, 12:54
Joined
Jan 14, 2017
Messages
18,186
I agree with Pat. Use one table for both internal and external support so that there is no duplication of data.
Use appropriate fields to indicate status of the various items
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:54
Joined
Feb 19, 2002
Messages
42,970
You should use a second "child" table to record data regarding the testing and resolution. So the main table has the initial problem description and the child table has all the resolution information. Add a column to the table so you can mark items as InternalAlso. Then you can have a a combo in the testing and resolution table that identifies whether the item refers to the internal or external solution.

The reason for maintaining a single table is to facilitate reporting and analysis and everything else.
 

Users who are viewing this thread

Top Bottom