Setup Table for Information query form (1 Viewer)

kurt114

New member
Local time
Yesterday, 22:49
Joined
Aug 15, 2019
Messages
2
Hi,

I'm new to Access. I want to setup a small database for work which manage records of information request.

For example I have department A, B and C. Information request form consists of a requester and responder. Either department (A,B or C) can be responder or requester.

My current setup is a table call tblRequest that includes all the fields for the information request, and a table call tblDept includes the department (A,B,C)

Should I link the table and how? or just use tblDept as a list for responder/requester?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:49
Joined
May 7, 2009
Messages
19,169
should there be any Status of the request?
more or less you will need:
Code:
tblStatus
ID                          autonumber
Status                    short text

tblDepartment
ID                           autonumber
Department             short text

tblRequestHeader
ID                            autonumber
RequestDate             date/time
StatusID                   number, long                  ID from status table
RequesterID              number, long                  ID from department table
ResponderID             number, long                  ID from department table

tblRequestDetail
RequestID                number, long                   ID from request header
Details                     short/long text

link tblRequestHeader to tblDepartment by RequesterID (left join)
link tblRequestHeader to tblDepartment by ReponderID (left join) (new link)
link tblRequestDetail to tblRequestHeader by RequestID (left join)
link tblRequestHeader to tblStatus by StatusID (left join)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:49
Joined
Feb 19, 2002
Messages
42,981
When you create the relationships using the relationship window or a query using the QBE, you will need to add tblDepartment to the grid TWICE. The second instance will be suffixed with "_1". So it will be named tblDepartment_1 to give it a unique name.

Then ResponderID will join to tblDepartment and RequestID will join to tblDepartment_1.

This is a conventional way to make sure that each join is separate and makes it easy to understand the relationships.

PS, I would never use "ID" as the name of every single autonumber. I would use a real name such as DepartmentID, RequestID, StatusID, etc in the table definition.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:49
Joined
Oct 29, 2018
Messages
21,358
Hi Kurt. Welcome to AWF!
 

kurt114

New member
Local time
Yesterday, 22:49
Joined
Aug 15, 2019
Messages
2
Thank you very much for your advice. I feel very welcomed :)
 

Users who are viewing this thread

Top Bottom