Append Query with Duplicate Fields 1

[naiku]

I need to set up an append query that will create a duplicate output field. In the table I wish to query I have:

Info 1 - Data A
Info 2 - Data B
Info 3 - Data B
Info 4 - Data C
(The above is a single record, Data A, B, and C are selected from a combo box where the row source is taken from a single source on a seperate table, but need to be stored in this table as seperate field entries)

I want to append this to a table where I have a single column for "Data" fields, so appending Data A, B and C into a single column of "Data". However Access does not let me do this, is this possible? I need to store the data above as a single record, but need to somehow group the Data A, B and C together so that I can create reports where I select for example Data B from the Data column and see:

Data B - Info 2
Info 3

I have thought about splitting my table's into smaller tables, but always end up with the same problem that I need the above as a single record, but need to create a report as per the above example.

Help please! This has been driving me crazy for a few days now and I always end up with the same problem. Many thanks.

 

[rac2]

Yes this is confusing. It seems easy enough but when we try to do that kind of thing with a database it may seem like we are doing a lot to get a small result.

We might be able to help you better if you give your example in concrete terms instead of with the abstract Data A and Info 2.

 

[naiku]

Ok no problems, the actual data for "Info" is for products, and the Data is for suppliers. I have a seperate table for both products and suppliers on a many to many relationship with a joiner table.

On a form I select products and suppliers, and the end result is that I want to print an order to send to a supplier. But do not want to print 4 orders for 1 supplier ordering 4 different products, I want to print 1 order for 1 supplier ordering 4 products.

The row source of my supplier combo boxes is based on the product/supplier joiner table where the supplier choice available is based on the choice of product selected in the previous field.

I need to save all the products/suppliers as a single record because they relate to a single job.

Thanks for your time.

 

[rac2]

It seems like you may need to introduce entities for Jobs and Orders. If there may be multiple orders for one job. Whether these both need tables or not would depend on whether you are keeping details about Jobs, my guess is that you will.

So there is a Jobs table with primary key job_id.
And an Orders table with primary key order_id and foreign keys to link to Products, Suppliers, and Jobs.

These are in addition to the association table, or joiner table which tells us that product Barracade is supplied by suppliers Bob, Fencemasters, and BarriersRus; or that Fencemasters supply Barracades, PortoPotties, and OrangeCones. (<-Humor).

The complete details of products from the suppliers for all orders in a job is

SELECT s.supplier_id, o.order_id, p.product_id
FROM Jobs j
JOIN Orders o ON o.job_id = j.job_id
JOIN Suppliers s ON s.supplier_id = o.supplier_id
JOIN Products p ON p.product_id = o.product_id
WHERE j.job_id = 311
ORDER BY s.company_name, o.order_id, p.category, p.name

Presumably each order is from one supplier, nonetheless that is a fact about that order that should be stored in the Orders table.

About this requirement
"all the products/suppliers as a single record because they relate to a single job."
As a general rule, for reasons I will not get into, you should never, ever do this in the database tables. Sometimes we like to combine data from multiple rows when we display it, but that should be accomplished in the Reporting or Display, not in the tables and the queries.

Does this get at the problem?

 

[naiku]

So there is a Jobs table with primary key job_id.
And an Orders table with primary key order_id and foreign keys to link to Products, Suppliers, and Jobs.

I don't have an Orders table, but reading through your reply it does make sense to have one. It was a possibility that I looked at, but was not sure how to make it work. I think I will give this a go, and then should hopefully be able to generate the reports that I need. Thankyou.

 

[naiku]

Would this work where I have several fields for suppliers in my jobs table? If I am reading the code above correctly then I will need to have a "supplier_id" field in my jobs table.

In my table I can choose for example, OrangeCones (item from a cones type combo box) which will then update a supplier choice combo box (based on the item I chose previously, to only display suppliers of that particular item data taken from the joiner table of products to suppliers) and that supplier will then be stored in my jobs table as ConesSupplier. I could then also have BlueLoo (again taken from a Loo combo box) select a supplier from an updated supplier list and then store this supplier in the table as LooSupplier.

If I am reading the code above correctly it will only work if I store my suppliers as a single supplier field "supplierID" in the jobs table.

Maybe I should somehow break up my jobs table so that instead of 1 record being:

JobID, Item 1, Qty 1, Supplier 1, Cost 1 Item 2, Qty 2, Supplier 2, Cost 2, Item 3, Qty 3, Supplier 3, Cost 3 etc.

That I have the records as:

Record 1 - Item 1, Qty 1, Supplier 1, Cost 1, Job1
Record 2 - Item 2, Qty 2, Supplier 2, Cost 2, Job1
Record 3 - Item 3, Qty 3, Supplier 3, Cost 3, Job1

The only problem I have then is getting the information onto a form to appear as 1 record instead of 3.

 

[PHV]

Take a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

Yes, break up the jobs table. You are going in the right direction. The essence of the FAQ posted by PHV (thanks), is that the arrangement of the data about a job can be accomplished in the presentation, not in the storage. The database is all about storage, not presentation.

The FAQ gives code in Visual Basic, I think, for arranging data from multiple rows into a single row. That is what you should do if you wish to have all of the data about a job on one line in a display such as a message on a form.


"...stored in my jobs table as ConesSupplier..."
When I read that, I think all jobs have a cone supplier and every job has exactly one cone supplier; and if they dont have one yet we can fully expect that they will have one before we are finished. Is that the case? Then you might make ConeSupplier an attribute of a Job. If cone supplier is a piece of data that is used to describe a job, then it could be a column in the jobs table.

But it may be that a job can have more than one cone supplier, or none at all.
In that case it is better to have a table for JobMaterials in which every row is some material that is used on that job, it may be any kind of thing, there might be a column to classify the things used in the job; one kind of thing could be cones.

It is not easy to design the tables and columns for an application. There are books on the subject. There are usually different solutions for the same requirements. I dont think we can give you the final answer here. It may be that your initial idea of columns in a jobs table for each kind of material will work for the situation at hand.

 

[naiku]

Thanks again for all the replies, over the weekend I broke up the table. My jobs table is now spread over 3 tables instead of the 1, and because of this fixing my original problem was a breeze.