Solved How to create fixed number of records

[thenoisydrum]

Sorry for the rubbish thread title but I couldn't what else to call it.
I am appending to an empty table customer name, brand and then sales. So, 3 columns.

I have 4 brands in total. Some customers do not buy all of the 4 brands but I need to display the 4 brands, later in a SELECT query.
For example, Dave buys all 4 brands but Chris only buys the 1 (beta).
The data looks like this;

But I need it to look like this;

I know that I have done it before but I cannot think of how to achieve it?
Any help would be appreciated

Thanks

Drum

 

[theDBguy]

You could try using a cross join/cartesian query.

Cartesian Query

When creating a query involving multiple tables, we can select from three types of table joins depending on the desired result of the query....

thedbguy.blogspot.com

 

[Josef P.]

select
  x.Customer, X.Brand, P.Date, P.Quantity, ...
from
(
  select 
     C.CustomerID, C.customer,
     B.BrandId, B.Brand
  from
     Brands as B
     cross join
     Customer as C
) as X
left join
  Purchases as P ON P.CustomerId = X.CustomerId and P.BrandID = X.BrandId

=> Source for pivot table.

 

[Pat Hartman]

In your order details form, you have a combo that selects the product the client DOES buy. If he buys 2 products, you end up with 2 detail rows. If he buys all 4, you end up with four rows. Do you think Amazon adds a million rows to each order just in case someone orders one of every item they sell? Order details are added one at a time as items are selected. Don't get hung up on FOUR. Tomorrow, your company may sell FIVE items and you would have serious redesign on your hands.

 

[The_Doc_Man]

My colleagues have provided you with ideas on how to do this. But here is the catch: For properly normalized data, you NEVER actually store empty records long-term. Instead, you use queries to build the temporary image that you want. And with that in mind, Josef P's suggestion for a pivot table is entirely appropriate.

 

[Pat Hartman]

If you want to show four rows with quantity of 0 and then let the user change the 0 to 1, then you do that with a left join between product and OrderDetails. But, I do not recommend this design. The user will find it confusing.

 

[Pat Hartman]

Gentlemen, the sample didn't show a crosstab. It showed a list with "missing" items which is a left join.

 

[Josef P.]

Gentlemen, the sample didn't show a crosstab. It showed a list with "missing" items which is a left join.

I hope that the data source does not have a field with a 'month-year' name. Therefore I thought of a pivot table.

 

[ebs17]

Simple queries are based on a good database schema. I see a many to many relationship here.

Customer => Sales <= Brands
Sales contains IDCustomer, IDBrand, SalesDate, Amount

didn't show a crosstab

Hopefully not a table (after a January 24th there is a 25th and a 26th and so on), but the result of a crosstab query = pivot table.

 

[thenoisydrum]

Thanks to you all of you for your replies