473,322 Members | 1,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Subform and queries

Friends,
My database has 5 different tables (tbl1, tbl2, tlb3, tbl4, and tbl5)
with the same fields: ID, SSN, LNAME, FNAME, RECEIVED and CLOSED. The
ID is the PK and its autonumber.

Obiously different data is stored in each table according to the type
of work my office handles. The database is used for a small insurance
company.

I have created a form on which added subforms each of which has it's
record source to a different query based on each of the above tables.I
give criteria to these queries using a form:
forms![myform]![mytxtbox]. It works fine. The form opens and displays
all the subforms with its data (SSN, FNAME, LNAME, RECEIVED and
CLEARED).

Since I would like not to repeat data like SSN, FNAME or LNAME more
than once, I was thinking of a way to display this information only
once. The problem is I could use one subform as the master one to
display the SSN, FNAME and LNAME, adn the other ones to display all
the other information, but it could be that that specific table (tbl1)
does not have that data but this has only been stored in tbl2 and
tbl3.

I believe the only way I can do this is using a query based on all the
5 tables an setting the criteria to the SSN again. But still cannot
get it working.

Can any one help me with the solution?
Nov 12 '05 #1
3 1525
DFS
Paolo,

The first part of the solution is to redesign your database. Combine the 5
work tables into one, and create a Customer table:
WorkTable
---------------------
WorkID *
WorkType ++
CustomerID ++
Received
Closed (or Cleared)
(other fields from tbl1, tbl2, etc)
Customer
---------------------
CustomerID *
SSN +
LastName
FirstName
(other fields)
Asterisk is primary key, + and ++ indicates unique index across fields.


"Paolo" <jp***@tin.it> wrote in message
news:9f**************************@posting.google.c om...
Friends,
My database has 5 different tables (tbl1, tbl2, tlb3, tbl4, and tbl5)
with the same fields: ID, SSN, LNAME, FNAME, RECEIVED and CLOSED. The
ID is the PK and its autonumber.

Obiously different data is stored in each table according to the type
of work my office handles. The database is used for a small insurance
company.

I have created a form on which added subforms each of which has it's
record source to a different query based on each of the above tables.I
give criteria to these queries using a form:
forms![myform]![mytxtbox]. It works fine. The form opens and displays
all the subforms with its data (SSN, FNAME, LNAME, RECEIVED and
CLEARED).

Since I would like not to repeat data like SSN, FNAME or LNAME more
than once, I was thinking of a way to display this information only
once. The problem is I could use one subform as the master one to
display the SSN, FNAME and LNAME, adn the other ones to display all
the other information, but it could be that that specific table (tbl1)
does not have that data but this has only been stored in tbl2 and
tbl3.

I believe the only way I can do this is using a query based on all the
5 tables an setting the criteria to the SSN again. But still cannot
get it working.

Can any one help me with the solution?

Nov 12 '05 #2
Thanks,
I realize that this is the best solution, but at this stage I cannot
do this since the database is used in over 40 Units and by over 400
users. If I redesign it now I will need to import data from the old
tables to the new ones and this should be done via code as I would be
sending the update file via e-mail. See, my program is splitted and I
use the _mdb file to sent out updates. The program is used for
statistical purposes and cannot afford to lose data or corrupt
integrity. I will need to import that data from the tables, exactly as
they are showing now.

THanks.

"DFS" <no****@nospam.com> wrote in message news:<10*************@corp.supernews.com>...
Paolo,

The first part of the solution is to redesign your database. Combine the 5
work tables into one, and create a Customer table:
WorkTable
---------------------
WorkID *
WorkType ++
CustomerID ++
Received
Closed (or Cleared)
(other fields from tbl1, tbl2, etc)
Customer
---------------------
CustomerID *
SSN +
LastName
FirstName
(other fields)
Asterisk is primary key, + and ++ indicates unique index across fields.


"Paolo" <jp***@tin.it> wrote in message
news:9f**************************@posting.google.c om...
Friends,
My database has 5 different tables (tbl1, tbl2, tlb3, tbl4, and tbl5)
with the same fields: ID, SSN, LNAME, FNAME, RECEIVED and CLOSED. The
ID is the PK and its autonumber.

Obiously different data is stored in each table according to the type
of work my office handles. The database is used for a small insurance
company.

I have created a form on which added subforms each of which has it's
record source to a different query based on each of the above tables.I
give criteria to these queries using a form:
forms![myform]![mytxtbox]. It works fine. The form opens and displays
all the subforms with its data (SSN, FNAME, LNAME, RECEIVED and
CLEARED).

Since I would like not to repeat data like SSN, FNAME or LNAME more
than once, I was thinking of a way to display this information only
once. The problem is I could use one subform as the master one to
display the SSN, FNAME and LNAME, adn the other ones to display all
the other information, but it could be that that specific table (tbl1)
does not have that data but this has only been stored in tbl2 and
tbl3.

I believe the only way I can do this is using a query based on all the
5 tables an setting the criteria to the SSN again. But still cannot
get it working.

Can any one help me with the solution?

Nov 12 '05 #3
Paolo:

Sounds like you need a Union query as the data source for your parent
form, so that all the SSNs are captured:

(SELECT SSN, LNAME, FNAME FROM tbl1)
UNION (SELECT SSN, LNAME, FNAME FROM tbl2)
UNION (SELECT SSN, LNAME, FNAME FROM tbl3)
UNION (SELECT SSN, LNAME, FNAME FROM tbl4)
UNION (SELECT SSN, LNAME, FNAME FROM tbl5);

You can't do that query in the GUI interface; you have to go into SQL
view and paste it in.
Unsolicited comment: As another respondent mentioned, your database
schema is truly horrible.

For instance, unless you have guaranteed that each unique SSN has
only 1 unique LNAME and FNAME tied to it, then when you run the Union
query above you can have a given SSN show up more than one time. That
in turn can cause data to be duplicated, triplicated, or worse. Which
can *really* mess up things.

One way to mitigate the duplicated SSN problem is to have the Union
query above pull *only* the SSN. Then display the name info via some
other means (perhaps a separate subform).

You said the database was used for statistical purposes; imagine how
badly your stats can be messed up by duplicated data. I urge you to
think a little bit more about redesigning your database, rather than
dismissing the suggestion outright.

Good luck!

-Matt


On 28 Feb 2004 03:10:57 -0800, jp***@tin.it (Paolo) wrote:
I believe the only way I can do this is using a query based on all the
5 tables an setting the criteria to the SSN again. But still cannot
get it working.


Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: A Clark | last post by:
I have a subform that displays a datasheet. There are a couple of different queries that can be displayed in the datasheet. The queries are available in a combo box on the main form. The problem...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
0
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
12
by: Paul T. RONG | last post by:
Is it possible to divide a tall subform with 80 records to two subforms each with 40 records? Dear All, What I have: Tables: tblProduct, tblOrder, tblOrderDetail
4
by: Ig | last post by:
Hi, I have a main form, subform in it, and two command buttons on the main form - "Save and Close" and "Cancel and Close". What code should I put into "Cancel and Close" button in order to undo...
30
by: Shannan Casteel via AccessMonster.com | last post by:
I have a subform named "sbfrmParts" with a list of parts along with the quantity and price. I have used a text box in the subform's footer and set the control source to "=Sum(*)". I set the...
8
by: Robert | last post by:
I have a form (Worksheet) that works fine by itself. I have now created a seperate form (MainForm) that has a command button in the header and an unbound subform (FormFrame) in the Detail section....
4
by: aqua404 | last post by:
I know this has been discussed, but I can't find a resolution. I have a subform on a form. The table with the data for the main form has 15,000 records. I am opening and then setting...
11
by: mrowe | last post by:
I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.