Solved

Access Web App: Primary & Foreign Key, Fetch Values?

Posted on 2016-10-03
12
109 Views
Last Modified: 2016-10-06
Hello,

I have a doubt related to Access Web App

I have 2 Tables:

      
Table1: TA1ID (pk) [AutoNumber],                            A, B, C, D               [Related Table]
Table2: TA2ID (pk) [AutoNumber], TA1ID (FK),     E, F, G, H, I, J, K, L  [Main Table]


My questions:

1. I want Values from A Table1 to E Table2. where should i have the foreign key Table1 or Table2 ?

how should i have it?

  TA1ID in Table2 as Foreign key
  TA2ID in Table1 as Foreign Key

What data type to be used?

      Example:      If i enter 12345 in A [Table1],
                          it should automatically be added to E [Table2] (Not as Dropdown list)
                  
                          If i update 12345 to 134 in A [Table1]
                          it should automatically be Update to E [Table2] (Not as Dropdown list)

                                If i delete Record 134 in A [Table1]
                                it should automatically delete in E [Table2]also.

2. I want A values to be Fetched into E, B values into F, automatically (update, delete should be easily done)

      Note: Not in Dropdown list (if i use lookup i will be able to get values but we have to type manually and select)
            

3. What data type to be used in both tables for A & B where values is entered in Table1 and fetched into Table2.

4. How do we create a Referential Integrity as we do create in Access Desktop?

5. When i create a lookup in access desktop it shows option to create Referential Integrity & Update & Delete Records. when i do the same in Access Desktop it doesn't happen that way it just shows a Dialogue box to lookup values from but no Referential integrity to update or delete records from related tables.


Thanks.
0
Comment
Question by:Satish Kumar G N
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41825937
Answers..

For 1. Main table will have Primary Key. Related table will have Foreign Key. Data Type of both the fields should be same. Normally it should be Integer.

Now if you insert something in the main table then you have insert the data in the related table also. It is part of referential integrity.


You have to enable the Delete and update cascade if you want delete and update feature.

For 2.  For this you have to write a Query to handle this scenario.

For 3. Seems like you are storing strings so use Varchar data types.

For 4. Once you create primary and foriegn key constraint in the access DB the database engine will automatically takes care of this.

For 5. If you sent all above it will be automatically be taken care by the system.

Enjoy! Let me know in case any issues.
0
 
LVL 1

Author Comment

by:Satish Kumar G N
ID: 41825946
Hi pawan,
thanks for your reply, your answers are correct but for the Access Desktop. In access Webapp these don't work. There is no option to enforce referential integrity , update and delete cascade when you select lookup in AccessWeb.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41825953
Ok so is there any option of custom coding ?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:Satish Kumar G N
ID: 41825996
not in access webapp, that's what I am trying to figure out. So I posted the question to seek answers for the same.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41826034
Okies, Actually I got confused with MS Access DB. !!
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41826036
Can't you take data from existing connection like SQL SERVER ?
0
 
LVL 85
ID: 41826077
You create "relationships" in an Access Web App as described in this MSFT article:

https://support.office.com/en-us/article/Create-a-relationship-40c998dd-4875-4da4-98c8-8ac8f109b85b?ui=en-US&rs=en-US&ad=US&fromAR=1#__create_a_relationship_webapp

That page should also answer your other questions.

Also, I'd suggest you take a little time to ensure the WebApp is going to provide you with the right environment. As of now, the Access WebApp is not the most robust platform, and while that may change right now, if you need anything more than a very basic CRUD application you're probably not going to be satisfied with an Access WebApp.
0
 
LVL 1

Author Comment

by:Satish Kumar G N
ID: 41826112
Hi scott,
thanks for your reply, but that doesn't answer my question fully, only till 6th point in Web Access section is clear, post that its unclear , not properly understandable for basic level people.

Thanks
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 41826140
That's the way "relationships" are managed in Access WebApps, so I'm not sure how much more information we can provide. As mentioned earlier, WebApps are not really ready for prime time, so there are some shortfalls you cannot get around.

Pawan answered your questions regarding how to structure your tables (i.e. the "child" table should store the Primary Key value of the "parent" table), as well as your #2 and #3 questions. The answer to you #4 question is "you can't do that in a WebApp", and the article I provided you explains why (and how to work around it, as much as possible). Your #5 question would be moot, since what you're asking doesn't really have any correlation to the WebApp world (you'd use the article I linked to provide that sort of functionality).

If you're not able to implement these features in your WebApp, I'd suggest you review some of the basic tutorials regarding WebApps. Microsoft has quite a few of them, and there are other sites around the internet that provide them as well (some are free, others have a cost).

Exactly what are you having troubles with, after following the steps outlined in that article?
0
 
LVL 21

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 400 total points
ID: 41827459
web apps relationships can have referential integrity -- it will only be available, however, if the fields are the same data type (foreign key is Whole Number) and the foreign key is linking to the primary key (AutoNumber) in the main table (or perhaps a field with a unique index)

You cannot put the same text fields in both tables and expect them to be able to get updated without any kind of valid link (and if you have a valid link, you don't need to repeat them -- this is not proper either).  Keep those text fields in the main table and NOT in the related table.  The related table should just have a whole number foreign key (FK) with a relationship to the primary key (PK) in the main table.  Once the FK gets a value, everything on the record in the main table with the corresponding value for PK is known too.

example:

Main table:

MainID = Autonumber PK = 2
ProjectName = Learn Access
ProjectManager = Satish

Related table:

RelatedID = Autonumber PK = 15
MainID = Whole Number = 2 --> Foreign Key related to main table PK with referential integrity
more fields ... but NOT ProjectName or ProjectManager  since those are in the main table
0
 
LVL 1

Author Comment

by:Satish Kumar G N
ID: 41829195
Hi crystal, thanks for reply, I have mentioned in detail as what I am trying to do in question Access Webapp: Table to Query to Table
there in comments I have mentioned what I am looking for, could u please use my data and answer as how to do it and I am replying for all abandoned questions as pointed out by you thanks , I didn't knew this part of EE.

thanks
0
 
LVL 1

Author Closing Comment

by:Satish Kumar G N
ID: 41831492
Thank you crystal and scott for constantly helping me out.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question