Hi,
I've created a form where a user has to enter a product and corresponding options and colours. He can enter 1 to 5 products with those additional options & colours. The product names and both options and colour lists are separate tables that i've inserted on the form as dropdownmenus with lookup fields.
So for instance:
Product1 - Option1 - Colour1
Product2 - Option2 - Colour2
...
Product5 - Option5 - Colour5
In total this makes 15 separate columns in my main table.
Now I would like to make a pivot table that shows for each product name the count of the chosen options and the chosen colours. I can't figure out how to do this since all three data are written accross five columns.
Thank you so much in advance for your help.
- a newbie -
9 1175 twinnyfo 3,653
Recognized Expert Moderator Specialist
jannabe,
Please provide us the structure of your tables (all tables involved).
What you are asking for is definitely doable, but we need to understand your table structures, first.
OK here are the table structures:
TProduct:
- ProductID (automatic)
- Productname (text)
TOption:
- OptionID (automatic)
- Product (lookup of TProduct.Productname en TProduct.ProductID)
- Optionname (text)
TColour:
- ColourID (automatic)
- Colour (text)
TMain:
- OrderID (automatic)
- Product1 (lookup TProduct.Productname)
- Option1 (lookup TOption.Optionname)
- Colour1 (lookup TColour.Colour)
- Product2 (lookup TProduct.Productname)
- Option2 (lookup TOption.Optionname)
- Colour2 (lookup TColour.Colour)
- Product3 (lookup TProduct.Productname)
- Option3 (lookup TOption.Optionname)
- Colour3 (lookup TColour.Colour)
- Product4 (lookup TProduct.Productname)
- Option4 (lookup TOption.Optionname)
- Colour4 (lookup TColour.Colour)
- Product5 (lookup TProduct.Productname)
- Option5 (lookup TOption.Optionname)
- Colour5 (lookup TColour.Colour)
twinnyfo 3,653
Recognized Expert Moderator Specialist
What is tblMain used for? From a DB normalization standpoint, we would always recommend not using a "wide" table, but rather a tall one, in which each Order may have multiple products. According to your table, you are wasting resources when someone orders only one product, but you have even greater problems if someone orders more than five.
A proper way to do this would be to have an orders Table, which lists the OrderID and the CustomerID. Then, an OrderDetails Table that lists all the products ordered with that one order ID.
If you have something else going on that doesn't fit that description, please explain.
Either way, still doable, but this gets us moving in the right direction (DB structure, etc.) first, then we'll talk about operations.
I've opted for this solution because there can be maximum 5 products ordered but it has to be clear which option and which colour is selected for each product.
So for instance my products are cars then I need to know for each car which option (eg. sports edition) and which colour (eg. blue) is selected.
twinnyfo 3,653
Recognized Expert Moderator Specialist
I keep trying to respond to this thread, but I am getting a network error.....
twinnyfo 3,653
Recognized Expert Moderator Specialist
jannabe,
At least I understand your structure, which helps. Of course, as a DB guy, my first response wil be, "But this would be so incredibly simple if your table was normalized..."
However, based on your current structure, the "concept" I will throw at you is to create five queries, one for each "set" of products, e.g., - SELECT ProductX, OptionX, ColourX
-
FROM TMain
-
WHERE ProductX Is Not Null;
Just change the "X" to the number of the Product Set.
Then, use a union query, which will produce your "tall" Table.
Use the results in an Aggregate Query (not a pivot table), and your results shoudl look like this: - Product Option Colour Count
-
Car Sports Blue 1
-
Car Sedan Red 3
-
Widget Deluxe Silver 12
-
Widget Custom Green 312
-
Widget Custom Purple 75
-
etc.....
twinnyfo 3,653
Recognized Expert Moderator Specialist
BTW, been trying to provide sample of the UNION Query, but it keeps producing a network error (just the code for the Union Query).
many many many thanks. At least now I know what to do :-)
zmbd 5,501
Recognized Expert Moderator Expert
jannabe:
When you say: Product2 (lookup TProduct.Productname)
Do you mean that in the table, you are using a lookup field?
Are you publishing this database to a SharePoint Site?
Also what version of Access are you using?
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bajal Mohamed via SQLMonster.com |
last post by:
I am facing problem like;
We generate reports thru Excel Pivot table. The data is coming from sales
cube. Let?s assume that there are 2 products A and B. The user wants to
filter for each...
|
by: Grey |
last post by:
I have created a asp.net form for user to input data. After input the data,
user need to click a button to export the input data to excel for data
analysis with excel pivot table function. is it...
|
by: Tim_k |
last post by:
Does anyone have an example of how to export a datagrid to an Excel pivot
table? The code below exports the grid contents to Excel using the Response
object. I'd like to expand it to show the...
|
by: kingster |
last post by:
Hi,
I have a regular dataset and all i want to do is make a pivot table
display in a browser with the datasource of the pivot table to be this
dataset and then the end-user will be able to do...
|
by: Jerome Ranch |
last post by:
Okay
So I've got pivot tables setup in Access 2003.
Only about 30K records in the current 2005 databases...the pivots
summarize the info in a number of nice ways.
I need to get the pivot tables...
| |
by: jodyblau |
last post by:
I am attempting to learn to use pivot tables.
I have noticed that if I have used the Lookup Wizard when creating my
tables so allow the user to select from a list of choices when entering
data,...
|
by: JayDawg |
last post by:
Is there any way to create a pivot table in a report? I have a query
with the fields Date, Name, and Data, and I am trying to create a
report that has the dates running across the top (like...
|
by: clickon |
last post by:
Does anyone know of a site outlining a good method of implementing a Pivot
table style grid using aGridView control or similar?
|
by: PeteCresswell |
last post by:
I've got something called "Reference Rates".
The idea is that on a given day, we have various rates of return for
various entities.
e.g. Libor 3-month return, Libor 6-month return, US Treasury...
|
by: Clare CAVS |
last post by:
I have a table with a lookup column referring to another table .
tblRooms has two fields, (Autonumber), and . The column I want to display is the RoomName column. If I have Bound Column = 1,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |