Prev: Access 2007 Read Only Issue
Next: Keith, Your information was just what I needed.
From: Jerry Whittle on 8 Feb 2010 13:38
That's just not going to work. Here's what to try.

1. Open the Relationships window and join the 4 tables there. Enable
Referential Integrity. If it won't let you, there may be a problem with data
especially with the Cut Table table as it doesn't have a primary key.

2. Create a form based on just the CustomerT table.

3. Create a subform on the above form based on the EstimateT. Since you have
the relationships built up above, the subform Wizard should do most of the
work.

4. Repeat for the other two tables.

It's possible that you will need sub-subforms, but your SQL statement seems
to say that the other 3 tables are linked directly to the CustomerT table.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Lenee" wrote:

> Jerry you are absolutely correct. I have 4 tables in my query. Here is the
> information you are asking for.
>
> Customer Table - Primary Key is CustomerID
> Estimate Table - Primary Key is EstimateID
> CutWork Table - Primary Key is CutworkID
> Cut Table - No Primary Key
>
> The CustomerID is in all of the tables and joined here.
>
> SELECT CutT.CutCust2010, CutWorkT.Cutwk1, CutT.CutArea, CutT.CutOrdNum,
> CustomerT.ABC, CustomerT.Address, CustomerT.City, CustomerT.State,
> CustomerT.Zip, CustomerT.MapPage, CustomerT.MapPageLetter,
> CustomerT.MapPageNum, CutWorkT.Cutwk2, CutT.CutPrice, CutT.CutEOW,
> CutT.Cutcomments, EstimateT.SqFt, EstimateT.Services, CutT.CutLR,
> CustomerT.CustomerID, CustomerT.MyCounter, CustomerT.FirstName,
> CustomerT.LastName
> FROM ((CustomerT INNER JOIN CutT ON CustomerT.CustomerID = CutT.CustomerID)
> INNER JOIN EstimateT ON CustomerT.CustomerID = EstimateT.CustomerID) INNER
> JOIN CutWorkT ON CustomerT.CustomerID = CutWorkT.CustomerID
> WHERE (((CutT.CutCust2010)=Yes) AND ((CutWorkT.Cutwk1) Is Null))
> ORDER BY CutT.CutArea, CutT.CutOrdNum;
>
> I hope this helps you to help me.
> --
> Lenee
>
>
> "Jerry Whittle" wrote:
>
> > I'm betting that the query contains more than one table. This can be a
> > problem.
> >
> > Show us the SQL. Open the query in design view. Next go to View, SQL View
> > and copy and past it here.
> >
> > Information on primary keys and relationships would be a nice touch too.
> > --
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> >
> > "Lenee" wrote:
> >
> > > I have created a datasheet form from a query everything is there. I am trying
> > > to enter data here but it will not except it here. Any help would be greatly
> > > appreciated.
> > > --
> > > Lenee
From: Lenee on 8 Feb 2010 15:11
Jerry I was able to make the sub-forms but what I am trying to do is get
information from each table on 1 datasheet to enter data. I need to see
customer name with there area (different table) and the cut week (a third
table), so that i can enter the data in the cut week field for the customers
that were cut that week. I am looking to enter 90 to 120 entries in a day.
Doing the sub-forms still only lets me look at 1 record at a time. Am I
trying to do the impossibe here?

I did go to the Relationships and did as you suggested, even put in a
primary key, still didn't work.

Thanks for your help and any other advise.
--
Lenee


"Jerry Whittle" wrote:

> That's just not going to work. Here's what to try.
>
> 1. Open the Relationships window and join the 4 tables there. Enable
> Referential Integrity. If it won't let you, there may be a problem with data
> especially with the Cut Table table as it doesn't have a primary key.
>
> 2. Create a form based on just the CustomerT table.
>
> 3. Create a subform on the above form based on the EstimateT. Since you have
> the relationships built up above, the subform Wizard should do most of the
> work.
>
> 4. Repeat for the other two tables.
>
> It's possible that you will need sub-subforms, but your SQL statement seems
> to say that the other 3 tables are linked directly to the CustomerT table.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Lenee" wrote:
>
> > Jerry you are absolutely correct. I have 4 tables in my query. Here is the
> > information you are asking for.
> >
> > Customer Table - Primary Key is CustomerID
> > Estimate Table - Primary Key is EstimateID
> > CutWork Table - Primary Key is CutworkID
> > Cut Table - No Primary Key
> >
> > The CustomerID is in all of the tables and joined here.
> >
> > SELECT CutT.CutCust2010, CutWorkT.Cutwk1, CutT.CutArea, CutT.CutOrdNum,
> > CustomerT.ABC, CustomerT.Address, CustomerT.City, CustomerT.State,
> > CustomerT.Zip, CustomerT.MapPage, CustomerT.MapPageLetter,
> > CustomerT.MapPageNum, CutWorkT.Cutwk2, CutT.CutPrice, CutT.CutEOW,
> > CutT.Cutcomments, EstimateT.SqFt, EstimateT.Services, CutT.CutLR,
> > CustomerT.CustomerID, CustomerT.MyCounter, CustomerT.FirstName,
> > CustomerT.LastName
> > FROM ((CustomerT INNER JOIN CutT ON CustomerT.CustomerID = CutT.CustomerID)
> > INNER JOIN EstimateT ON CustomerT.CustomerID = EstimateT.CustomerID) INNER
> > JOIN CutWorkT ON CustomerT.CustomerID = CutWorkT.CustomerID
> > WHERE (((CutT.CutCust2010)=Yes) AND ((CutWorkT.Cutwk1) Is Null))
> > ORDER BY CutT.CutArea, CutT.CutOrdNum;
> >
> > I hope this helps you to help me.
> > --
> > Lenee
> >
> >
> > "Jerry Whittle" wrote:
> >
> > > I'm betting that the query contains more than one table. This can be a
> > > problem.
> > >
> > > Show us the SQL. Open the query in design view. Next go to View, SQL View
> > > and copy and past it here.
> > >
> > > Information on primary keys and relationships would be a nice touch too.
> > > --
> > > Jerry Whittle, Microsoft Access MVP
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > >
> > >
> > > "Lenee" wrote:
> > >
> > > > I have created a datasheet form from a query everything is there. I am trying
> > > > to enter data here but it will not except it here. Any help would be greatly
> > > > appreciated.
> > > > --
> > > > Lenee
From: Lenee on 8 Feb 2010 15:14
Steve you were right. Is there any way to make it updateable?
--
Lenee


"Steve" wrote:

> Your query is probably not updateable. Open the query and look at the
> navigation buttons. If the new record button os grayed out, your query is
> not updateable. When a query is not updateable, you won't be able to add new
> records to a form based on the query. For example, union queries and
> crosstab queries are not updateable.
>
> Steve
> santus(a)penn.com
>
>
> "Lenee" <Lenee(a)discussions.microsoft.com> wrote in message
> news:A5BB1169-7B01-4686-A0AF-8B6C4D9FA487(a)microsoft.com...
> >I have created a datasheet form from a query everything is there. I am
> >trying
> > to enter data here but it will not except it here. Any help would be
> > greatly
> > appreciated.
> > --
> > Lenee
>
>
> .
>
From: Steve on 8 Feb 2010 15:34
Lenee,

Your tables probably need a simple tweak! I presume you want to enter an
estimate for a customer. Is that correct? Please explain Cut Table and
CutWork Table. Is CutWork the details of a Cut? Could you respond back with
the fields in the Estimate Table, Cut Table and CutWork Table in this
format:

Estimate
EstimateID
<your fields>

Cut
<your fields>

CutWork
CutworkID
<your fields>

Steve


"Lenee" <Lenee(a)discussions.microsoft.com> wrote in message
news:65238FDD-A873-4E50-8EAD-B765558DC89F(a)microsoft.com...
> Steve you were right. Is there any way to make it updateable?
> --
> Lenee
>
>
> "Steve" wrote:
>
>> Your query is probably not updateable. Open the query and look at the
>> navigation buttons. If the new record button os grayed out, your query is
>> not updateable. When a query is not updateable, you won't be able to add
>> new
>> records to a form based on the query. For example, union queries and
>> crosstab queries are not updateable.
>>
>> Steve
>> santus(a)penn.com
>>
>>
>> "Lenee" <Lenee(a)discussions.microsoft.com> wrote in message
>> news:A5BB1169-7B01-4686-A0AF-8B6C4D9FA487(a)microsoft.com...
>> >I have created a datasheet form from a query everything is there. I am
>> >trying
>> > to enter data here but it will not except it here. Any help would be
>> > greatly
>> > appreciated.
>> > --
>> > Lenee
>>
>>
>> .
>>


From: John W. Vinson on 8 Feb 2010 16:37
On Mon, 8 Feb 2010 12:11:01 -0800, Lenee <Lenee(a)discussions.microsoft.com>
wrote:

>Jerry I was able to make the sub-forms but what I am trying to do is get
>information from each table on 1 datasheet to enter data. I need to see
>customer name with there area (different table) and the cut week (a third
>table), so that i can enter the data in the cut week field for the customers
>that were cut that week. I am looking to enter 90 to 120 entries in a day.
>Doing the sub-forms still only lets me look at 1 record at a time. Am I
>trying to do the impossibe here?

Try changing the subform's Default View property from "Single Form" to
"Continuous Forms". It's perfectly possible to see one "parent" table record
(Customer in your case) with multiple "child" table records on one screen, if
you design your queries correctly.

Your attempt to create One Great Master Query to do everything is, in this
case, doomed to failure since there is no physical or logical relationship
between the Estimate and the CutWork tables - each is related to the Customer,
but it appears that they are not related to each other.
--

John W. Vinson [MVP]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Access 2007 Read Only Issue
Next: Keith, Your information was just what I needed.