How to create a Matrix style query?

[Benaal]

Hi,



I have (for the sake of argument) three tables - Widgets (Number 'ID', text 'WidgetName'), Factories(Number 'ID', text 'FactoryName'), and FactoryWidgetLink(Number 'ID', Number 'Factory', Number 'Widget', Number 'Throughput').

The link table columns Factory and Widget, are linked in the relationship diagram to the IDs in the respective tables.


the desired output from the query i'm trying to design is that all of the factories are listed across the column headers of a matrix, and all of the widgets are listed along the row headers of the matrix, and all intersections shown on the matrix, regardless of whether there is a record for it or not (Add a 0 throughput record for each if necessary).

I'm trying to present the user with a way of defining the throughput easily for each widget type in each factory. I don't want them to have to fill in a form for each entry.

Any ideas?

Thanks in advance.

[orange]

Often, presenting data is a different step than capturing data.

You could probably adapt a Crosstab query for presentation purposes.
For capture you might use a couple of combo or list boxes (Factory and Widget), make a selection from each and enter a Production/throughput number.
My suspicion is that you will want some sort of Time/Week/Shift element to your overall design.

see attached:
Cross tab sample design

CrossTab with Data