Transforming Rows into Columns

[tonygg]

Hi. I was hoping for some advice. I have been given a DB design task at school. We have a table containing services that have been requested by customers. Each row is:



Customer-Email, Request-Date/time, Authorised-by, Service-Name, Discount, Served-Price, Served-Location, Served-Date/Time, Total-Price

A customer can make a request for multiple services at one time so there can be for 1 customer request multiple rows (1 row per service requested).

I have created this service request table along with a table of customers and a table of possible services.

I need to output a list of all customers with their requested services. "Each record MUST show: customer email, names of the requested services, serviced location, serviced price, discounts, paid price for each service, and total price of each request".

I think this means I need to group the output by customer request and show each service as a set of columns:

customer email, total price of each request, repeating columns for each service (names of the requested service, serviced location, serviced price, discounts, paid price for each service)

I am struggling to see how to get the repeating columns for each service? Pivot tables only seem to allow one repeating value rather than a set.

Any pointers would be greatly appreciated.

Many thanks

tony

[andy49]

Order the select query on customer id?


Sent from my iPhone using Tapatalk

[tonygg]

Thank you Andy. This will give me one row per service.

I actually need one row per request and in that row a repeating set of columns per service (name of the requested service, serviced location, serviced price, discounts, paid price for service) .

[rpeare]

I would like to know your table structure because that would affect your approach.

for instance you should have something like

tblCustomers
C_ID  C_Name -----> other customer related fields
1     Customer 1
2     Customer 2

tblCustomerContacts
CC_ID  C_ID  CC_FirstName  CC_LastName  CC_Email
1      1     Person        One          pone@fake.com
2      1     Person        Two          ptwo@fake.com

tblOrder
O_ID  O_Date    C_ID  ---> other order related fields
1     1/1/2017  1
2     1/2/2017  1
3     1/3/2017  3

tblItem
I_ID  I_Name  ---> other item related fields
1     Item 1
2     Item 2
3     Item 3

tblOrderDetails
OD_ID  O_ID  I_ID  OD_Quantity  ----->
1      1     1     5
2      1     2     10
3      2     3     5
4      2     1     25

If this is similar to your structure you can handle displaying this stuff on a report by the creation of groups/group headers to show and suppress information from appearing on every line.

[tonygg]

Hi rpeare. Thank you.

The structure is the same as the one which you have listed. However we are not allowed to use dataless keys for this assignment so I struggle with having tblOrder and tblOrderDetails as they both have compound primary keys and I dont know how to keep referential integrity with compound keys. I may have misunderstood how to create the tables but here is what i did. I have the following tables:

CUSTOMER
PK (Customer Email) and other related fields

SERVICE
PK ( Service-Name) and other related fields

STAFF
PK (Staff Email) and other related fields

REQUEST

CREATE TABLE  REQUEST([Customer-Email] VARCHAR(15)  NOT NULL,[Service-Name] VARCHAR(30)   NOT NULL,[staff-Email] VARCHAR(15)  NOT NULL,[Request-Date/time] DATE/TIME NOT NULL,[Authorised-by]  VARCHAR(15)  NOT NULL,[Served-Date/Time] DATE NOT NULL,[Request-Date/time] DATE NOT NULL,[Discount] decimal(3,2) ,[Served-Price] DECIMAL(10, 2), [Served-Location]  VARCHAR(15)  NOT NULL,[Total-Price] DECIMAL(10, 2), CONSTRAINT REQPK PRIMARY KEY([Customer-Email],[Service-Name],[Request-Date/time],[Served-Date/Time]),CONSTRAINT REQSE FOREIGN KEY([staff-Email]) REFERENCES STAFF([staffEmail])    ON DELETE SET NULL    ON UPDATE CASCADE,CONSTRAINT REQCE FOREIGN KEY([Customer-Email]) REFERENCES CUSTOMER([CustomerEmail])    ON DELETE CASCADE    ON UPDATE CASCADE,CONSTRAINT REQSCE3 FOREIGN KEY([Service-Name]) REFERENCES SERVICE([Service-Name])    ON DELETE SET NULL    ON UPDATE CASCADE); 

So REQUEST has the compound Primary key: [Customer-Email],[Service-Name],[Request-Date/time],[Served-Date/Time]

If I was to split it into 2 tables like you did (REQUEST AND SERVICE) I would :

REQUEST:
PK ([Customer-Email],[Request-Date/time])

and

SERVICE:
PK ([Customer-Email],[Service-Name],[Request-Date/time],[Served-Date/Time])

SERVICE would need to have the primary key of REQUEST as a Foreign key to ensure there are no service records that do not have requests. I dont know who to code this using the Primary keys as I have created them.

Does this make sense? Any ideas?

Many thanks once again.

Tony

[Micron]

Any pointers would be greatly appreciated.
- NEVER use special characters in your table or object names (except possibly the underscore _) although I'm too lazy to type that one continually.
- DON'T store calculations (such as the total amount of an invoice). The rare exception is when it is necessary to maintain history if certain values are subject to modification and those values are used in past and future calculations for things like reports or invoices. The preferred approach to avoid over-writing those values is to store the value as a historical one; e.g. the price paid and not a link to the price of the inventory item which is subject to change.
- relational tables should only store data that pertains to the attributes of the entity, along with fields that permit you to link one table to another (FK's). So tblRequest should not contain customer data (such as email addresses).
- Assuming a schema has been well thought out, based on the principles of Normalization, one should construct queries BEFORE forms. If you cannot retrieve the data you need, nor edit it in the query, you probably have not done something correctly (notwithstanding that some queries are not editable, such as Totals or Union queries). A form based on a faulty query can result in a huge waste of time.

For your task, I'd advise creating sample blocks of Excel cells laid out like your table(s), complete with field headings and dummy data. If the columns are properly sized, you copy and paste each block separately here and get a nicely laid out table with usually no trouble. Then we can see things more clearly and try to find pitfalls. If you are posting that you have a 4-field PK in tblService, I'm going to go out on a limb and say 'no way'. Also, what is a data-less key? To me, that would be one with Null values which probably doesn't make sense. If you're saying you cannot use an PK autonumber from one table as a FK in another, that's another thing (I wouldn't call the FK data-less since the PK id is a piece of data, but that's just me being picky). You'd have to make text or numerical values to be your PK values.

[tonygg]

Hi Micron,

I mean autokey. So I cant use autokeys as Proimary keys. This means I need to use compound primary keys. The data is laid out in the same way as rpeare is showing. One of the big issues for me is how to create the primary keys and maintain referential integrety between the request (order) and service provided (products sold). The base data I have been given (un-normailised):

Computer-Surgery: {Customer-name (Customer-Firstname, Customer-Lastname), CustomerEmail, Customer-Tel, Customer-Address (House-No/Name, Post-Code, City, County, Country), Customer-Age, Customer-Feedback, Customer-Feedback-Score, Customer-Type, Request-Date/time, Authorised-by, staff-name(Staff-Firstname, staff-Lastname), staff-Email, {staff-Qualifications}, Service-Type, Service-Description, Service-Name, Service-Price, Discount, Served-Price, Served-Location, Served-Date/Time, Total-Price}.

To identify a Request (like an order) I need to use CustomerEmail and Request-Date/time as composite primary key.

To Identify a Service (like a product bought in an order) I need to use CustomerEmail, Request-Date/time, Service-Name and Served-Date/Time as composite primary key.

I have tried to build the Service table in Access but I can seem to tell it that part of the primary key (CustomerEmail, Request-Date/time) is also a primary key to the table Request. It does not understand. I am now stuck and dont know how to progress other than creating a single table for Request and service:

The combined table would have the primary key: CustomerEmail, Request-Date/time, Service-Name and Served-Date/Time.

Diagrammatically I end up with the following. I have shown the combined table as a cloud as it does not seem fully normalised:





Is there another way without using autokeys?

Many thanks

Tony

[Micron]

how to create the primary keys and maintain referential integrety
You can either construct a composite primary key or a composite index.
Disclaimer: I for one see no reason not to use composite PK's but there are others here who don't. We've had discussions on this a couple of times, the current status being I haven't changed my mind yet. That being said, any field(s) in a composite index can be part of a relationship IIRC. Use the relationships window to create relationships between the required fields, whether or not they are part of a composite index or PK. You might want to enforce cascading deletes or updates as well. Note that I already said your queries have to be correct - if you create a relationship, the many side of it cannot be appended to without the one side existing already, so you may find that imposing relationships after the fact will break queries that worked before.

I haven't fully digested your setup, so I'm not agreeing or disagreeing with your assertion that you must have composites because you cannot use autonumber data as FKs. There is no direct cause/effect on which to base this assumption, so if you need composites, it must be for some other reason. One reason that I haven't digested your schema is because of the way you continue to represent it and to some degree, the names you continue to use.

Hopefully I have cleared up the issue of composites and related fields. One of your difficulties continues to be that you are repeating fields in tables (mostly email type), which should not be happening, mostly because it is an odd identifier for a customer or staff member. If the name changes due to corporate takeover or marriage, the email address will likely change, which means you need to propagate this value throughout the db, with the added difficulty of it being both a PK and FK somewhere. Normally, that sort of data cascade is OK on regular data, but if done properly, there should be no reason at all to alter the PK of a customer record. This is why it's preferable to use a numerical value of some sort, if not an autonumber. And why should you not use / and - in your field names? There must be a few good reasons, one being that you will probably encounter an error when Access or Jet tries to subtract/divide one word from/by another. It happened to me before I knew better.

[ssanfu]

Is there another way without using autokeys?

I think you mean "Autonumber" type fields.
I am really curious as to why can't you use an "Autonumber" type field for the PK field?

Also note that it is not ideal to use text fields as PK fields (as noted by Micron).


I am providing these links for general knowledge:
Microsoft Access Tables: Primary Key Tips and Techniques

Autonumbers--What they are NOT and What They Are

[tonygg]

Hi Ssanfu. The reason is it is a school exercise and they have said we cannot use autonumber. I agree it does not make sense but I dont make up the rules!.

Hi Micron. Thank you for your explanations. As it is an exercise I cant use "autonumber" so I need to pick from the available fields PKs. This is leading to the need to repeated use of things like client-email in primary keys along with use of date and time fields.

If I understood Micron correctly I can have composite PKs and use the composite PK as a composite foreign key in a related table to enforce referential integrity? I am struggling to write SQL that MS Access 2016 will accept to enforce this. I was hoping some one could help me structure the MS Access SQL:

I will have a REQUEST table: PK (CustomerEmail, Request-Date/time)

I will have a SERVICE-PROVIDED table : PK (CustomerEmail, Request-Date/time, Service-Name, Served-Date/Time).

I need to ensure you cant have a SERVICE-PROVIDED without a REQUEST. So I need to have a CONSTRAINT in the SERVICE-PROVIDED table that says something like:

CONSTRAINT REQSERVICE FOREIGN KEY([CustomerEmail],[Request-Date/time]) REFERENCES REQUEST([Customer-Email],[Request-Date/time]) ON DELETE CASCADE ON UPDATE

Unfortunately Access says this is not correctly formed (does not work).

If I try:

CONSTRAINT REQSERVICE1 FOREIGN KEY([CustomerEmail]) REFERENCES REQUEST([Customer-Email]) ON DELETE CASCADE ON UPDATE
CONSTRAINT REQSERVICE2 FOREIGN KEY([Request-Date/time]) REFERENCES REQUEST([Request-Date/time]) ON DELETE CASCADE ON UPDATE

Access says the the REQUEST fields are not unique (correctly) and cant be used.

Please could someone let me know the correct SQL to form the foreign key relationship?

Many thanks

Tony

[Micron]

Surely they have not also dictated that you build these tables and relationships by using sql instead of just going to table design view as well as using the relationships window??
If that's the case, this is certainly not a beginner course. To employ a constraint clause in a sql statement for composite keys look here
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx
and here
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

[tonygg]

Hi Micron. Thank you again. I guess they are not assuming we access to MS Access and so it is done through theory rather than by using tools. I have now managed to get the composite keys working.

I was hoping for one other design related question. We need to allow for customers to provide feedback on services provided. I am assuming that the feedback is optional so may not be provided on all (or any) services provided to a customer. If it is provided then there would be a 1 to 1 relationship between the feedback and a record in the SERVICE-PROVIDED table. We need to hold the data in 3rd normal form. I am not sure where I should put the feedback data. If I put it in the SERVICE-PROVIDED table then it would be sparsely populated (as the data is optional). if I put it in a separate table it would need to be related to SERVICE-PROVIDED but I am not sure what the PK should be on the table. I guess it needs to be the same as the primary key of the SERVICE PROVIDED table. Is there a design guideline for such situations?

Many thanks Again.

Tony

[Micron]

Feedback comments should go into their own table. If they are related to a service, then the fk in tblCmnts would have to be the PK of the tblService (if I understand the schema). If they are related to a request, then it would be that table. As long as you can relate the required FK(s) all the way through the chain, you should be able to successfully query the customer info, any request, any service provided, and any comments related to a service. If there are no comments related to a service provided, none of the PK's from the 'parent' tables will appear in the comments table, thus no empty fields in a record, which is what you'd get by putting comments into any other table. As for what the PK for this table might be, given that you cannot use autonumbers, I see no reason for a pk field. If 3 comments need to be logged in for a service, the service pk would be the fk in comments. Unless you know now that you will need to relate comments to some other child table in the future, I see no point in having a PK in tblCmnts. A PK is not an absolute requirement in every table. If autonumbers were allowed, I wouldn't say that.