Re: Query as data source for form
- From: "Jeff Boyce" <nonsense@xxxxxxxxxxxx>
- Date: Tue, 8 Jul 2008 11:29:34 -0700
My time in these 'groups, like most everyone else's, is voluntary.
You might want to consider continuing this thread, as you will get a lot
more folks looking over your descriptions and offering ideas...
Regards
Jeff Boyce
Microsoft Office/Access MVP
"News Boy" <NewsBoy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B7E01DBE-16E6-4BFB-9D55-2F9DBEE05DB3@xxxxxxxxxxxxxxxx
Things just go from bad to worse. Now I have created a table "carriers"
with
only 3 fields: ID (AutoNum key field), Last Name, and Account Number. My
Routes table has about 20 records filled, with a relationship between
carriers.AccountNumber and Routes.Carrier. The Carrier table has only 3
records filled. I made a Query in the Wizard which I intend to return all
the data from both tables, but it only returns three records. Therefore,
the
form I made to enter data into these tables only accesses three records,
and
will not create a new record at all.
Compounding the problem, I can't make head nor tail out of the help files
and have no clue how to use Design View. How do I Insert an interface to
a
given field, when the only options the Insert button gives me are Picture,
Object, Page Number, Date/Time, and ActiveX Control, which then lists a
bunch
of things I don't know about with no explanantion. Do you think you could
find it in your heart to talk with me directly or would I need to pay
extra
for a phone call?
- Dixon
"Jeff Boyce" wrote:
Dixon
The "sub1, sub 2, ... sub 'n'" is a dead giveaway! If you have (as many
as)
four (?3, 5, 9, ??) repeating columns in a table, you probably have a
one-to-many relationship. To get the best use of Access'
relationally-oriented features/functions, you need to look at creating a
new
"trelRouteSubs" that hold valid combinations of Routes and Subs.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"News Boy" <NewsBoy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6F230EF8-75C0-4140-BDFA-B5672EFABB34@xxxxxxxxxxxxxxxx
Thank you, Jeff. Here are the tables:
tblRoute
AutoNum
RouteNum
Carrier (Points to Carrier.AccountNum)
Sub1 (Points to Subs.AutoNum)
:
Sub4 (Points to Subs.AutoNum)
other route and map information fields
tblCarrier
AutoNum
AccountNum (Points to Routes.Carrier)
other personal information fields
tblSubs
AutoNum (points to Routes.Sub1 .. Routes.Sub4
other personal information fields
Then I should be able to pull up a report of which routes relate to
which
carriers, and which subs are available for which routes, and various
other
useful stuff.
I ran into a problem using AutoNum to identify the subs in that it
cannot
be
edited from a form, so I may have to add a tag field to the Subs table.
I have not been able to get the query to act as data source, and was
further
confused when the wizard created a form that accessed all the tables
simultaneously, but they were out of order. The help files say this is
impossible.
I will be heading into crisis mode here next week and probably only
able
to
work on it a few minutes a day, but things should calm down in a week
or
two.
Thanks again for your help.
Sincerely,
Dixon
"Jeff Boyce" wrote:
I'm still having a bit of difficulty "seeing" the
tables/relationships...
May I ask that you try posting a description something like:
tblPerson
PersonID
FirstName
LastName
DOB
tblClass
ClassID
ClassTitle
ClassDescription
trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate
(this is only an example)
Regards
Jeff Boyce
Microsoft Office/Access MVP
"News Boy" <NewsBoy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D129DA87-40B1-4407-A0A4-9BD23AA93CDB@xxxxxxxxxxxxxxxx
I'm sure it is I who do not understand relationships ... I have one
table
with fields RouteNumber, Carrier (which is the Carrier's Account
Nmber
off
the Carriers table), Sub1,Sub2, etc (which are the AutoIncrement
value
in
the
records for the Subs table). I would think that the field
Carriers.AccountNumber would have a one-to-one relation with the
field
Routes.Carrier.
The Route can have only one Carrier, but the Carrier can have more
than
one
Route.
The Subs are identified by their Auto Increment field because some
of
them
don't have an account number. The Routes table has 4 fields fo
relate
to
Subs.ID: Routes.Sub1, Routes.Sub2, Routes.Sub3, and Routes.Sub4. A
Route
can only have one Sub1, etc., but Subs can sub for more than one
Route.
I would think that Route.Subx fields would relate one-to-one with
Subs.ID,
so it's probably one-to-many like you say.
(There are other fields of information in the tables but they don't
relate
to one another.)
"Jeff Boyce" wrote:
Dixon
I'm not sure I understand the relationships... for instance, how
does
"one-to-one" come into the picture?
Regards
Jeff Boyce
Microsoft Office/Access MVP
"News Boy" <NewsBoy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:27CFCE9E-3933-42A2-8156-4B3CFF7D03D2@xxxxxxxxxxxxxxxx
Yes, thank you, Mr. Boyce. I stumbled upon that subform thing by
constructing simple practice forms with one or two fields per
table.
I
haven't discovered how to define one-to-one relationships yet;
the
wizard
simply tells me "one-to-many" or "indeterminate" so far.
I do have more than one route per carrier; the Routes table has
one
route
per record with the carrier,s account number as one field.
Therefore,
the
account number is unique on the Carriers table but duplicated on
the
Routes
table. Subs are related to the Routes table via the fields Sub1,
Sub2,
etc
which all point at the Auto ID field fo the Subs table.
I am hoping to be able to enter and modify data like when a new
carrier
takes over a route, to just enter the new account number in the
tag
field
of
the Routes table, etc. and it looks like somehow the subform idea
will
work.
I prefer elegance to brute force, and strive for a deeper
understanding
of
how this Access software functions. Your help and feedback is
very
much
appreciated.
- Dixon Stuelke
"Jeff Boyce" wrote:
I'll take a guess that you have "one-to-many" relationships in
there
somewhere (e.g., one carrier, many routes). When you join all
the
tables
together, Access has no way to know WHICH record to update
unless
you
get
very specific.
Instead of trying to cram all the data from all the tables into
a
single
form, consider using a main form (?carriers) with a sub-form
(?routes),
and
that subform could have a subform of its own (?subs). There are
a
lot
of
questions marks in there because I don't know your data, so I
don't
know
what the relationships are...
Regards
Jeff Boyce
Microsoft Office/Access MVP
"News Boy" <News Boy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DD175B05-F63A-4BC8-B4D8-2AE3475C7FBD@xxxxxxxxxxxxxxxx
I have data in 3 tables called Carriers, Routes, and Subs. I
can
make a
form
display data from one table at a time. But, to access multiple
tables,
a
query is necessary. I have created a query in Query Wizard
which
displays
the relationships between the tables, etc. In Design View,
with
the
Query
as
data source, the form displays all the field names of all the
tables
properly. But the form will not access any of the records in
the
tables.
The help files suggest that the query may not be outputting
any
result,
but
does not say what to do to fix this.
.
- References:
- Re: Query as data source for form
- From: News Boy
- Re: Query as data source for form
- Prev by Date: Re: Query one table against part of a cell of another
- Next by Date: Re: Formulas
- Previous by thread: Re: Query as data source for form
- Next by thread: SQL WHERE = Query Results
- Index(es):
Relevant Pages
|
