Multi Table Queries

At my job, I use enter data through generated queries. However it appears 
that I can only ENTER data in a query when it is based on two tables. If it 
is three or more, it doesn't let me enter any info.

Is this normal? Am I doing something wrong?

-Pete
0
Utf
12/7/2007 4:06:02 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
588 Views

Similar Articles

[PageSpeed] 55

Pete wrote:

>At my job, I use enter data through generated queries. However it appears 
>that I can only ENTER data in a query when it is based on two tables. If it 
>is three or more, it doesn't let me enter any info.
>
>Is this normal? Am I doing something wrong?


Yes, that's common.  You should only edit data in a single
table at a time.  Forms and subforms are the mechanisms
intended for entering/editing data in (multiple) tables.

-- 
Marsh
MVP [MS Access]
0
Marshall
12/7/2007 4:31:54 PM
You can create queries with many tables and keep them updateable. The "key" 
is the "key". If you join primary to foreign keys 1 to many through mutliple 
"generations", the results can be updateable. I have one application where I 
think I use 6 tables and the results can be updated.

-- 
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP 
http://www.access.hookom.net/UCP/Default.htm


"Marshall Barton" wrote:

> Pete wrote:
> 
> >At my job, I use enter data through generated queries. However it appears 
> >that I can only ENTER data in a query when it is based on two tables. If it 
> >is three or more, it doesn't let me enter any info.
> >
> >Is this normal? Am I doing something wrong?
> 
> 
> Yes, that's common.  You should only edit data in a single
> table at a time.  Forms and subforms are the mechanisms
> intended for entering/editing data in (multiple) tables.
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
12/7/2007 4:53:02 PM
Duane Hookom wrote:

>You can create queries with many tables and keep them updateable. The "key" 
>is the "key". If you join primary to foreign keys 1 to many through mutliple 
>"generations", the results can be updateable. I have one application where I 
>think I use 6 tables and the results can be updated.


Oh my!

Are you also allowing edits in that kind of query's
datasheet or in some sophisticated form?

-- 
Marsh
MVP [MS Access]
0
Marshall
12/7/2007 8:14:21 PM
It's a datasheet view on a subform. I have most of the fields locked and 
allow entry into only about 3 fields. This is an inspection database 
application and this form allows entry of inspection results.

Here is the formatted SQL view:

SELECT tblFacility.facFacID, tblFacility.facFacility, tblAreas.areAreID,
tblAreas.areDescription, tblSites.sitSitID, tblSites.sitDescription,
tblInspections.insComplDate, tblInspections.insEnterDate,
tblInspections.insEnterBy, tblInspections.insResID,
tblInspectionTypes.ityInspectionType, tblInspectionPoints.poiDescription,
tblInspections.insInspectDate, tblInspections.insRespPSEmpID,
tblInspections.insInsID, tblInspections.insAuditBy
FROM tblInspectionTypes
INNER JOIN (tblInspectionPoints
INNER JOIN (tblFacility
INNER JOIN (tblAreas
INNER JOIN (tblSites
INNER JOIN tblInspections ON tblSites.sitSitID = tblInspections.insSitID)
ON tblAreas.areAreID = tblInspections.insAreID)
ON tblFacility.facFacID = tblInspections.insFacID)
ON tblInspectionPoints.poiPOIID = tblInspections.insPOIID)
ON tblInspectionTypes.ityITyID = tblInspections.insITyID
ORDER BY tblFacility.facFacility, tblAreas.areDescription,
tblSites.sitDescription, tblInspectionTypes.ityInspectionType,
tblInspectionPoints.poiDescription;
-- 
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP 
http://www.access.hookom.net/UCP/Default.htm


"Marshall Barton" wrote:

> Duane Hookom wrote:
> 
> >You can create queries with many tables and keep them updateable. The "key" 
> >is the "key". If you join primary to foreign keys 1 to many through mutliple 
> >"generations", the results can be updateable. I have one application where I 
> >think I use 6 tables and the results can be updated.
> 
> 
> Oh my!
> 
> Are you also allowing edits in that kind of query's
> datasheet or in some sophisticated form?
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
12/7/2007 9:50:01 PM
Duane Hookom wrote:

>It's a datasheet view on a subform. I have most of the fields locked and 
>allow entry into only about 3 fields. This is an inspection database 
>application and this form allows entry of inspection results.
>
>Here is the formatted SQL view:
>
>SELECT tblFacility.facFacID, tblFacility.facFacility, tblAreas.areAreID,
>tblAreas.areDescription, tblSites.sitSitID, tblSites.sitDescription,
>tblInspections.insComplDate, tblInspections.insEnterDate,
>tblInspections.insEnterBy, tblInspections.insResID,
>tblInspectionTypes.ityInspectionType, tblInspectionPoints.poiDescription,
>tblInspections.insInspectDate, tblInspections.insRespPSEmpID,
>tblInspections.insInsID, tblInspections.insAuditBy
>FROM tblInspectionTypes
>INNER JOIN (tblInspectionPoints
>INNER JOIN (tblFacility
>INNER JOIN (tblAreas
>INNER JOIN (tblSites
>INNER JOIN tblInspections ON tblSites.sitSitID = tblInspections.insSitID)
>ON tblAreas.areAreID = tblInspections.insAreID)
>ON tblFacility.facFacID = tblInspections.insFacID)
>ON tblInspectionPoints.poiPOIID = tblInspections.insPOIID)
>ON tblInspectionTypes.ityITyID = tblInspections.insITyID
>ORDER BY tblFacility.facFacility, tblAreas.areDescription,
>tblSites.sitDescription, tblInspectionTypes.ityInspectionType,
>tblInspectionPoints.poiDescription;


Interesting.  I don't think I've ever run into a situation
where I needed more than three tables and even then I ran
into trouble occasionally.

With that many fields, you probably reduce clutter by
setting ColumnWidths to 0 for at least the ID columns.

-- 
Marsh
MVP [MS Access]
0
Marshall
12/7/2007 11:49:57 PM
There are a couple of the ID fields that aren't displayed in the datasheet 
form. The others match values displayed in a paper report used to conduct 
inspections on the factory floor. Since the joins are all primary/foreign 
keys, the query remains updateable.

-- 
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP 
http://www.access.hookom.net/UCP/Default.htm


"Marshall Barton" wrote:

> Duane Hookom wrote:
> 
> >It's a datasheet view on a subform. I have most of the fields locked and 
> >allow entry into only about 3 fields. This is an inspection database 
> >application and this form allows entry of inspection results.
> >
> >Here is the formatted SQL view:
> >
> >SELECT tblFacility.facFacID, tblFacility.facFacility, tblAreas.areAreID,
> >tblAreas.areDescription, tblSites.sitSitID, tblSites.sitDescription,
> >tblInspections.insComplDate, tblInspections.insEnterDate,
> >tblInspections.insEnterBy, tblInspections.insResID,
> >tblInspectionTypes.ityInspectionType, tblInspectionPoints.poiDescription,
> >tblInspections.insInspectDate, tblInspections.insRespPSEmpID,
> >tblInspections.insInsID, tblInspections.insAuditBy
> >FROM tblInspectionTypes
> >INNER JOIN (tblInspectionPoints
> >INNER JOIN (tblFacility
> >INNER JOIN (tblAreas
> >INNER JOIN (tblSites
> >INNER JOIN tblInspections ON tblSites.sitSitID = tblInspections.insSitID)
> >ON tblAreas.areAreID = tblInspections.insAreID)
> >ON tblFacility.facFacID = tblInspections.insFacID)
> >ON tblInspectionPoints.poiPOIID = tblInspections.insPOIID)
> >ON tblInspectionTypes.ityITyID = tblInspections.insITyID
> >ORDER BY tblFacility.facFacility, tblAreas.areDescription,
> >tblSites.sitDescription, tblInspectionTypes.ityInspectionType,
> >tblInspectionPoints.poiDescription;
> 
> 
> Interesting.  I don't think I've ever run into a situation
> where I needed more than three tables and even then I ran
> into trouble occasionally.
> 
> With that many fields, you probably reduce clutter by
> setting ColumnWidths to 0 for at least the ID columns.
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
12/8/2007 3:17:01 AM
Reply:

Similar Artilces:

Remember selections in Table Names Lookup window (Dex Runtime)
In Microsoft Dynamics GP, the user can view details on database tables in the Table Descriptions window (Tools>>Resource Descriptions>>Tables). If the user clicks the elipsis (...) button to the right of the table name, a lookup window opens. The user needs to choose both the product, series, and view by option that he or she is working with, and the list of tables will display. At least 10 times a day I go into this window, and oftentimes I don't select the table I really want first. That means I need to do a second lookup, select the product, series, and view by aga...

How to save "Empty" as Pivot Table calculated item result, or else...??
Not sure if this is the right forum, but - Have a pivot table in which I've created a calculated item. The data set contains several different data groups, and I use an attribute field as a page field to select the group of interest. So far, so good. Then I create a calculated field of the type Profit by subtracting Cost from Price. When I do so, the page field no longer selects the group of interest - rather, the whole data set shows up, regardless of page field election. In looking at the calculation results, if I subtract Cost [when tests True for Empty] from Price [also when test...

Query about COM
Can Any One sugest me a best book for understanding COM Basis. Give me any best links. OR send me the some importance things in COM or any other tutor to my id it_srini@yahoo.com. pls help me. Essential COM by Don Box Inside COM (Programming Series) by Dale Rogerson Un saludo Rodrigo Corral Gonz�lez [MVP] microsoft.public.es.vc FAQ http://vcfaq.europe.webmatrixhosting.net Absolutely the best book I have ever read on the subject is Essential COM by Don Box. "Srii S" <it_srini@gawab.com> wrote in message news:ufAlyj3fEHA.1656@TK2MSFTNGP10.phx.gbl... > Can Any One ...

How to save query output as a table??
What I would like to do is run a query, but then save the query output as a new table. I can work out how to save the query, but not the result How do I do this please? Thanks -- John Perry http://www.redoak.co.uk http://www.maytrees.co.uk John Perry wrote: > What I would like to do is run a query, but then save the query output > as a new table. I can work out how to save the query, but not the > result > > How do I do this please? Make the query into a Make Table query or use it as the input to a Make Table query. If you press the "Query" item in the menu ...

Web query cannot find URL. Why?
I am unable to get to square one with a web query. When I enter nces.ed.gov (a legitimate URL) in Field 1 of the "new Web Query" dialog window, I get an error message that says, "The address of this site is not correct. CHeck the address and try again." Yes, it is the correct address. Why do I get this error. I can open IE to the URL with no problems. How do "Internet Connections" in IE 6.0/Tools/Internet Options/Connections have to be set up for web queries to work? Is this what the problem is? THank you. John Wirt I just tested this using nces.ed.gov...

Web Query problem
I have created a web query in Excel to download "key statistics"for a stock into my spreadsheet. The url and query is http://finance.yahoo.com/q/ks?s=PFE Selection=yfncsumtab,21,24,26,29,32,35,38,41,44 Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False DisableRedirections=False with the ticker being PFE. I want to be able to reference a cell in the spreadsheet with a ticker so I can easily change the stock I am requesting data for. Can you tell me how to do that? -- Al Eaton aleaton@bak.rr....

Append Data To Access Table
There is an Access table on the network. 15 users who do not have Access are connected to the network. Is there a way for each user to be able to enter one or more rows containing 3 or 4 columns to Excel on his machine and then press a button or something and append that data to the Access table on the network? Also would need to delete the data from the worksheet after the append. How would I prevent more than one user from appending data at the same time? Any suggestions on what the code would be? Thank you very much! Martin ...

Table Setup 11-02-07
I'm new to access and have been asked to build an employee performance quality control database for my company. I can't seem to located an Access template for employee QA to study before I build mine. Can you help me to locate a sample database for this type of situation. Thank you. Don't know of any samples, but could help you start one from scratch if needed. "SITCFanTN" wrote: > I'm new to access and have been asked to build an employee performance > quality control database for my company. I can't seem to located an Access > template for e...

Query Text Size
Is there a way to increase the text size when writing queries, either in design view or SQL view? Just to make it easier on my eyes.... On the File Menu 1.. On the File menu go to the Tools menu, click Options. 2.. Click the Tables/Queries tab. 3.. In the Query design font group, select the font and font size. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II <ed0211e@gmail.com> wrote in message news:b0a088ff-97f1-42c1-986c-097bb1c44a2e@41g2000hsc.googlegroups.com... > Is there a way to increase the text size when writi...

Selecting one column from an XML table
Hi Folks, I have a ADO.NET Dataset in the following format: <Dyelots> <Batch> <batch_id>38084</batch_id> <shade_name>03880</shade_name> <article>2994120</article> .... </Batch> <Dyelots> Presently this data is loaded into a data grid from which screen a series of reports can be run using the string from DataSet.GetXML as the criteria for each report. Now, because, in total, each batch row has about 25 columns and there can be any number of batch rows in a table the size of the XML string sent across the network...

Name to Initials in table
I have a table that has the fields FIRST NAME, LAST NAME and MIDDLE NAME. I would like to add another field to this same table that is called Initials and I would like this field to generate the initials based on the other three fields. Is this possible in my table? I have tried this statement: Left([FIRST NAME],1) & Left([MIDDLE NAME],1) & Left([LAST NAME],1) in a query and it works fine but I would like the field to be in my table as I would like to link this table to another database that would use the initials. It would be a mistake to store it in your table. ...

query for values not in use
I've got a table (tblSeat) which just holds the numbers associated with seats. There are 200 seats. Record #1 holds Seat 1, #2 2, and so on. I've then got another table (tblEvents) that holds events associated with people listed in a third table (tblMain). Each person in tblMain is also associated with a reference number (tblRefNum); there is one RefNum for a group of people in tblMain. I record into tblEvents that a person in tblMain who is associated with a particular RefNum was just seated in seat #. There are many events in tblEvents for each person in tblMain (one:Man...

pivot tables and acceptance...
here's my dilemma: i have a list of standings for a school wrestling team. the data looks like this... bob here 19-Sep win bob there 20-Sep lose larry here 19-Sep win larry there 20-Sep win joe here 19-Sep win joe there 20-Sep lose .... and i have a "current standings" pivot table on another sheet showing who won on what date or against who. now say i add... bob here 21-Sep lose larry here 21-Sep lose joe here 21-Sep win .... immedeatly after in the next 3 rows. is there a way to have the pivot table automatically a...

Append Query Fails
I'm trying to run the following append query. The table Carroll contains 238 records. Each time I run the query it's appending a different number of records and seems to be very random. 183, 210, 192..... I'm running it from a button as follows: Dim stQuery4 as String stQuery4 = "CarrollAppend" DoCmd.OpenQuery stQuery4 DoCmd.Close acQuery, stQuery4 And here's the SQL....I'm really pulling my hair out on this and of course my client is calling me every 5 minutes. Thanks for any help!!! INSERT INTO CarrollMain ( ACCTBAL, LASTPMTDATE, CS66_PT...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Excel should automatically make multi-panel charts with common axe
I'm running Office 2003. I would like to be able to automatically generate multi-panel graphs from a common template that share an axis. Now I have to manually hide the axis labels and tick marks on the upper graph, and try to align things as evenly as possible. Maintaining equal graph and font sizes is difficult, and graphs aligned on screen often are not aligned when printed. A feature that automatically generated multi-panel graphs would be very much appreciated. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most vot...

I need to create a date/time search query but i cant figure it out!
Hi, I have a time-slot grid that I want to be able to click on and have access find out if there are any records based around that slot.. so basically, I have a varDate and a varTime and I need to use a query to query against my databases StartTime, EndTime, and AppDate variables.. in psuedocode i need to find the result of WHERE varDate = AppDate AND (StartTime <= varTime AND EndTime >= varTime) Ive never done a search using dates before. I was wondering if the formatting of the dates is important? I did do a basic query that seem to fail when just searching for dates.. the...

Linked Excel spreadsheet to be used as table
So I want to link an Excel spreadsheet to a table in access. Using the link manager I was able to create the table and I see all the information I need to see and it is linked to the original spreadsheet. My only problem is, when the spreadsheet is updated, it is not updating my access table. Is this even possible to do? How can I make sure this is done everytime the database opens? Any help would be much appreciated. "Stryder09" <Stryder09@discussions.microsoft.com> wrote in message news:A4C5C034-3F92-450E-ACB8-EF252E8C7B88@microsoft.com... > So I want t...

RMS Table Structure & Field Description
Hello, RMS table names are very self-explanatory. However, sometimes I'm having tough time identifying the fields in the tables and what are they for. Is there a list of field description and its types for some of RMS main tables?(ie: Transaction, TransactionEntry, etc.) For Example: I'm looking at Transaction table and with RecallID and RecallType, I have no ideas what RecallID '65' means and RecallType '1' means. Please help/ Contact me by email and i will send you a comprehensive doument on RMS Store and HQ tables and what they mean. Afshin Alikhani - afshi...

Pivot Table Sort #2
In a Pivot Table how do I sort by the sub-totals created by the pivot table fruit Name Apple Granny Smith Apple Golden Delicious orange Spanish Orange OutSpan Apple Grany Smith ----------------------------------------------------- Pivot table would read Fruit Name Totals --------------------------------------------- Apple Grany smith 2 Golden Delicious 1 --------------------------------------------- Apple Total 3 ---...

Highlight the drop down lists in pivot tables when other than "Sh.
It's difficult to tell if a pivot table is "filtered" by selecting other than "Show All" in a page, row or column element. Suggest you apply the same metaphor you currently use for filtered lists which changes the arrow to blue in the filter icon of a filtered column. Similarly, if other than "Show All" is selected, change the arrow to blue in the drop down list icon. ...

Re: Prompt Message Query
Hi all, Now, I know that we all hate Excel's paperclip 'helper'; however, have been asked to create a worksheet that once a cell has been fille in the l'il bastard pops up and gives a prompt for what the user shoul do next (e.g. 'Go to Sheet2'). I know that using Validation you can bring up a message when the cel is selected, and that if you're using a validation drop-down you ca summon the evil piece of twisted metal to give a message. However, can't find a way of bringing it up when you've simply completed a cell Any ideas? TIA, SamuelT PS - If you...

Access 2007
Maybe there are a lot of changes in access 2007 that I just dont understand, but I am having NO luck with it! First I tried to link to a Dbase 4 file on our network and it kept telling me it couldnt do it. Something about needing to open it exclusively. So late one night I had to do it when no one was using that db. Seems very odd since I commonly do the same thing (to the same databases) in older versions with no problems. I am not trying to open it excusively myself, and no one else has it opened exculsively either. Second, When I try to run search and replace operations I get many errors ...

Query problem
Well here I go again. Biting off more than my brain can handle. I have a report that shows all of our machines in our plant. I’m tracking both machine down time (the time the machine is broken) and Plant Down Time (the time the machine is broken that hampers production) I have a form that we use to gather all pertinent information such as the date, time notified of malfunction etc… I have a query that well, queries the data so I can view what is happening each month. That works great. Now to the meat and potatoes… I calculate down times by using: Minutes: DateDiff("n", [Time ...

is this query really too complex?
hello I am getting the error message that this is too complex! Can this be right? A limit on the number of IIFs? Is there another way of doing this? RenewalCost: IIF([1118] and [type]="a",100, IIF([1118] and [type]="c",60, IIF([1118] and [type]="d",130, IIF([1116] and [type]="a",85, IIF([1116] and [type]="c",50, IIF([1116] and [type]="d",100, IIF([1618] and [type]="a",85, IIF([1618] and [type]="c",50, IIF([1618] and [type]="d",100, IIF([1118plus] and [type]="a",100, IIF([1118plus] and [type]...