Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-10-2010, 10:09 AM   #1
ddmcmullan
Registered User
 
Join Date: Jun 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
ddmcmullan is on a distinguished road
Creating a report from Multiple Tables

If anyone can shed some light on this problem it would be a great help, I'm a bit of a novice on access so my problem may be easy to solve.

I am creating an access program for my dad's retail shop to create item signs (So everything is laid out nicely).

The tables I have are:
Package Sign: Package No (Primary, Autonumber), Total Price, Your Saving
Inventory: Package No, item description, item price
Options: Package No, option type, option price

Everything is linked to the package number pretty much with a one to one relationship.

I have the main form from table package sign and two subforms inventory and options.
From the main form I run a report which I would like to show:

Package No
Total Price
Your Saving

Option Type Option Price
Option Type Option Price
Opt .................................... etc

Item Description Item Price
Item Description Item Price
Item .................................. etc

At the moment when I run the report it joins all the information together and duplicates it, for example if I have two different option types it will duplicate by 2, 3 different option types duplicates by 3.

In actual fact I want the information to remain separated.

I've tried using the sub-form feature this lays it out how I want it but looks bad.

ddmcmullan is offline   Reply With Quote
Old 06-10-2010, 10:19 AM   #2
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,269
Thanks: 74
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Creating a report from Multiplace Tables

The way you describe your tables they are not setup properly.

Look for normalizing in your help.

You can't repeat the same name on your tables if they are the primary Key fields.

InventoryTable should be your main table and the Primary Key would be ProductID or SKUNumber - an auotnumber.

Any other tables will have their own Primary Key and may then include ProductID as a normal field.

The description of multiple results suggests your relationships are not right and or your select query is not right.

Check out the table structure, setup your table relationships and then work on your queries and from there your reports.

I would suggest you may not need a table PackageSign as this may be a calculated issue.
PNGBill is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
One report from multiple tables josephbupe Reports 3 03-22-2009 12:36 AM
[SOLVED] Report from query on multiple tables bhp59 Queries 2 07-27-2006 03:05 AM
multiple tables report at different levels naminder Reports 1 10-31-2005 03:29 AM
Creating Report based upon multiple fields orbic1 Modules & VBA 6 03-15-2004 03:14 AM
Combo Box Preview Report Mike Hughes Reports 10 03-10-2004 10:04 AM




All times are GMT -8. The time now is 12:16 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World