Creat a Report from Tables vs Queries (1 Viewer)

[Rae]

Hi - I am new here, I have researched the heck out of this and am coming up empty handed....and I have no clue how to do what I am trying to do. My quest:

Create one report from two tables where information has been entered

A little background: I own a home building company/lumberyard, some materials arrive custom order which are recorded on one table (PurchaseOrderMain) sorted by the customer name (combo box, predictive phrase) - the table includes fields: PurchaseOrder#, SupplierName, ProductDescription, CustomerName , OrderDate, Notes, Invoiced (check box), Invoice#, TotalCost, Quantity, SortCode etc......All custom orders for ALL customers is entered on this same table and sorted by the Customer Name.

Other materials from stock are entered on another table that are customer specific (is. John's Build (JohnHome) is a separate table from Rita's Build (RitaBuild) for stock items)

I want to create a report that brings the items from the PurchaseOrderMain table that relate to John's Build (John's name in the CustomerName field) AND all the items from the table JohnHome.

I want them grouped by SortCode which I can figure out myself but I am having trouble linking the tables and getting them together in a report.....I have created a query from the PurchaseOrderMain for CustomerName "John" only and that seems to work good but I still can't create a report from table+table or table+query.

Any help is greatly appreciated - if this seems impossible, I understand as I am about ready to throw in the towel anyways.

Thanks in advance.
Rae

 

[plog]

John's Build (JohnHome) is a separate table from Rita's Build

That's not how you should structure your tables. Table and field names should not contain specific information, that data should reside inside the table itself. So all those Build tables should be in one table called 'Build' with a new field which designates who the data is for (John, Rita, Dave, etc.).

Can you post your structure? The results you describe seem simple with the right structure, but I you've headed down the wrong path.

 

[Rae]

Thanks for your response this is the end result I want to achieve:

A detailed list of items and costs used per Customer to include items used that were special order and used from inventory.

Table #1 consists of the following fields:
PurchaseOrder#
SupplierName
ProductDescription
CustomName
OrderDate
TotalCost
Code

Table #1 is operated by different employees in our establishment. Each employee that places an order with a supplier with a PO number will enter the details of the PO on this Table #1.

Table #2 consists of the following fields:
Description
Quantity
Cost
Code

Table #2 is for items that have not been ordered in (no PO number) because they were ordered in as inventory and taken from inventory rather than special order. We use excel spreadsheets right now that we enter this information into specific to each customer.

Table #1 (PurchaseOrderMain) is up and running and all of the employees have access to this table to enter, search etc; so I thought if I could do away with the excel spreadsheets by creating customer specific tables to be incorporated with Table #1 there would be a lot less double entry.

 

[plog]

Table 1 tells you which customer each record belongs to, so you don't need a table for each customer.

Inventory control is a common subject on this forum and I am sure there are a lot of templates here (and on the web) that you can steal. Search the forum for these examples.

 

[jdraw]

I agree with plog - there is a structure issue. You have to get your tables and relationships set up to support your business. The sooner you do it, the less frustrated you will be with queries and reports etc.

There are fundamentals to database that you do not learn by jumping into Access or any other DBMS.


Watch the videos in post #4 at
http://www.access-programmers.co.uk/....php?p=1217446

Good luck