Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Reports (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=10)
-   -   Creating a report from Multiple Tables (https://www.access-programmers.co.uk/forums/showthread.php?t=194318)

ddmcmullan 06-10-2010 10:09 AM

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.

PNGBill 06-10-2010 10:19 AM

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.


All times are GMT -8. The time now is 03:02 PM.

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