Info not showing up..

[stephendeloach via AccessMonster.com]

I have a Form with 2 subforms, in the subforms one is for taxable and the
other is for non-taxable amounts. I then have a report running from a query
that gives me my information. When I have data in the taxable (subform1)
subform the report comes out fine. When I have data in the taxable and non-
taxable (subform2) subforms the report comes out fine also. When I just have
data in the non-taxable (subform2) subform, my report comes out with #error
and nothing in the "description"? Also, when I run the report it is putting
my “description†in twice from the taxable subform? Can someone please help
me? Thanks

 

[Guest]

Stephen,

Note that data is not contained in forms or subforms, it is stored in
tables. Forms are merely a vessel through which the data is viewed. The key
to understanding your problem is understanding the report's underlying
RecordSource. Open the form in design view, show properties by pressing F4,
and note the value of the RecordSource property on the Data tab. If it is
the name of a query, please post the SQL of the query--open the query in
design view, select View, SQL, and cut and paste.

Also post the ControlSource property of the description control.

Sprinks

 

[stephendeloach via AccessMonster.com]

My main forms RecordSource is Table1. Table2 subform recordsource is Table2
and Table3 subform recordsource is Table3. My reports record source is
Table1 Query. Here is the query in SQl view...

SELECT Table1.[BillTo:], Table1.WellName, Table1.[Parish/County], Table1.
InvoiceDate, Table1.[Invoice#], Table1.CompanyFrom, Table1.NetAmount, Table2.
Description, Table2.Quantity, Table2.UnitPrice, Table3.Descirption2, Table3.
Quantity2, Table3.UnitPrice2, Table1.Tax
FROM (Table1 INNER JOIN Table3 ON Table1.[Invoice#] = Table3.[Invoice#])
INNER JOIN Table2 ON Table1.[Invoice#] = Table2.[Invoice#];

I think this is more info than you asked for but hopefully it will help...
Thanks for your time.

Stephen

Stephen,

Note that data is not contained in forms or subforms, it is stored in
tables. Forms are merely a vessel through which the data is viewed. The key
to understanding your problem is understanding the report's underlying
RecordSource. Open the form in design view, show properties by pressing F4,
and note the value of the RecordSource property on the Data tab. If it is
the name of a query, please post the SQL of the query--open the query in
design view, select View, SQL, and cut and paste.

Also post the ControlSource property of the description control.

Sprinks

I have a Form with 2 subforms, in the subforms one is for taxable and the
other is for non-taxable amounts. I then have a report running from a query

[quoted text clipped - 5 lines]

my “description†in twice from the taxable subform? Can someone please help
me? Thanks

 

[stephendeloach via AccessMonster.com]

Sorry. Forgot the description. I have 2 descriptions in my form.
"Description" which is in Table2 subform and its control source is
"description" and I have "description2" in table3 subform and its control
source is "description2". In my report under the Detail section i have
"description" and directly under it is "description2" could this be my
problem?

My main forms RecordSource is Table1. Table2 subform recordsource is Table2
and Table3 subform recordsource is Table3. My reports record source is
Table1 Query. Here is the query in SQl view...

SELECT Table1.[BillTo:], Table1.WellName, Table1.[Parish/County], Table1.
InvoiceDate, Table1.[Invoice#], Table1.CompanyFrom, Table1.NetAmount, Table2.
Description, Table2.Quantity, Table2.UnitPrice, Table3.Descirption2, Table3.
Quantity2, Table3.UnitPrice2, Table1.Tax
FROM (Table1 INNER JOIN Table3 ON Table1.[Invoice#] = Table3.[Invoice#])
INNER JOIN Table2 ON Table1.[Invoice#] = Table2.[Invoice#];

I think this is more info than you asked for but hopefully it will help...
Thanks for your time.

Stephen

Stephen,

[quoted text clipped - 15 lines]

 

[Guest]

Yes, Stephen, that is the problem, and it is symptomatic of having a
non-normalized database structure. A cardinal rule of relational database
design is to avoid storing the same type of data in two different tables.
You should combine Table2 and Table3 into a single Charges table (or
Payments, whatever applies), and add a Yes/No Taxable field.

Then create two queries, each selecting the original three fields, with one
selecting those records where Taxable = True, the other where Taxable =
False. Set the RecordSources your two subforms to the appropriate query.
Then to simplify data entry, add a checkbox control to each bound to the
Taxable field. In the first subform, set its DefaultValue to True, and to
False in the second. Set the checkbox' Visible property in each subform to
No. Now you needn't worry about checking the box for each record, it will
automatically be set depending on which subform you are in.

One other note: your application is much more easily understood by others
(and you) when you use more meaningful table names. In fact, the process of
choosing the name is often clarifying--each field should be an attribute of
the table; if it's not it doesn't belong.

Hope that helps. See the following resources on database normalization.
Sprinks

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp

 

[stephendeloach via AccessMonster.com]

Im not that great with access but ill try and see what i can come up with.
Thanks for the help.

 

[stephendeloach via AccessMonster.com]

OK. I think i did it all right... I have created a query that i will run my
report from and after i created the query and ran it, nothing comes up? Here
is the query in SQL...

SELECT Table1.[BillTo:], Table1.WellName, Table1.[Parish/County], Table1.
InvoiceDate, Table1.[Invoice#], Table1.NetAmount, Table1.CompanyFrom, Table1.
Tax, Table2.UnitPrice, Table2.Description, Table2.Quantity, Table2.Taxable
FROM Table2 INNER JOIN Table1 ON Table2.ID=Table1.[Invoice#];

Thanks

Im not that great with access but ill try and see what i can come up with.
Thanks for the help.

Yes, Stephen, that is the problem, and it is symptomatic of having a
non-normalized database structure. A cardinal rule of relational database

[quoted text clipped - 49 lines]

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp

 

[Guest]

Stephen,

Probably the easiest way to move the records is to:

- Rename Table 2 to whatever meaningful name you want to use.
- Define a new query based on the newly named table. Drag the Taxable
field to the grid.
- Change to an Update query (Query, Update Query).
- In the Update To: row, enter True
- Press the Execute button (the ! button)
- Take a look at the table. Each record should have its checkbox checked.
- Begin a new query based on Table 3. Drag all fields to the grid.
- Change the query type to Append (Query/Append Query). Access will prompt
you for the table. Enter the newly-named table.
- In the Append To: row, enter the corresponding fields (Description, Qty,
Amount)
- Execute the query. It will add these rows to the new table, but leave
the Taxable field blank (False).
- Check that the new table has both sets of records. When sure, you may
delete Table3.
- Create a new select query based on the new table. Drag all fields to the
grid. In the Criteria: row, enter True in the Taxable column.
- Execute the query. It should select only those rows where Taxable is
true. If so, save under a meaningful name such as TaxableCharges, and exit.
- In the database window, highlight the new query, press Ctrl-C to copy it
to the clipboard, then Ctrl-V to paste it to the Query window. It will
create a duplicate query named TaxableCharges1. Rename it to something like
NonTaxableCharges. Edit the query, changing the True criteria to False.
Save and exit.
- Open your main form, click into the first subform, and click the small
square box at the top left of the subform window. Press F4 to show the form
properties. Click on the Data tab, and change the RecordSource property to
the query name TaxableCharges. Leaving the Properties window open, show the
field list (View, Field List). Drag the Taxable field to the subform. Click
on its label and press delete to remove it. Select the new checkbox control,
and change its Visible property on the Format tab to No. Change its
DefaultValue property on the Data tab to True.
- Save, then similarly change the 2nd subform, setting the RecordSource to
NonTaxableCharges, and the DefaultValue of the checkbox to False.

Hope that helps. If you have any other issues such as how to calculate tax
for the taxable items, let me know.

Sprinks

 

[stephendeloach via AccessMonster.com]

Ive treid but I cant get anything to work. Thanks for the help though.

Stephen,

Probably the easiest way to move the records is to:

- Rename Table 2 to whatever meaningful name you want to use.
- Define a new query based on the newly named table. Drag the Taxable
field to the grid.
- Change to an Update query (Query, Update Query).
- In the Update To: row, enter True
- Press the Execute button (the ! button)
- Take a look at the table. Each record should have its checkbox checked.
- Begin a new query based on Table 3. Drag all fields to the grid.
- Change the query type to Append (Query/Append Query). Access will prompt
you for the table. Enter the newly-named table.
- In the Append To: row, enter the corresponding fields (Description, Qty,
Amount)
- Execute the query. It will add these rows to the new table, but leave
the Taxable field blank (False).
- Check that the new table has both sets of records. When sure, you may
delete Table3.
- Create a new select query based on the new table. Drag all fields to the
grid. In the Criteria: row, enter True in the Taxable column.
- Execute the query. It should select only those rows where Taxable is
true. If so, save under a meaningful name such as TaxableCharges, and exit.
- In the database window, highlight the new query, press Ctrl-C to copy it
to the clipboard, then Ctrl-V to paste it to the Query window. It will
create a duplicate query named TaxableCharges1. Rename it to something like
NonTaxableCharges. Edit the query, changing the True criteria to False.
Save and exit.
- Open your main form, click into the first subform, and click the small
square box at the top left of the subform window. Press F4 to show the form
properties. Click on the Data tab, and change the RecordSource property to
the query name TaxableCharges. Leaving the Properties window open, show the
field list (View, Field List). Drag the Taxable field to the subform. Click
on its label and press delete to remove it. Select the new checkbox control,
and change its Visible property on the Format tab to No. Change its
DefaultValue property on the Data tab to True.
- Save, then similarly change the 2nd subform, setting the RecordSource to
NonTaxableCharges, and the DefaultValue of the checkbox to False.

Hope that helps. If you have any other issues such as how to calculate tax
for the taxable items, let me know.

Sprinks

Im not that great with access but ill try and see what i can come up with.
Thanks for the help.

[quoted text clipped - 52 lines]