Solved Combine Records (1 Viewer)

RevJeff

Registered User.
Local time
Today, 05:34
Joined
Sep 18, 2002
Messages
125
Hello all,

I have a table that looks like this:

Capture.JPG

It shows Customer Number, Name, CityState, and the days of the week with Delivery ETAs. Is there a way through code or a query where I can have customers with the same CustNo be on one line instead of the two. See below.

Capture.JPG


Thanks for any help you can offer.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,357
Hi. I think there is, but you would probably have to normalize your table structure first. For example, rather than have separate columns for each day, you only want two columns and then put the day and times in different rows (records). Then, you can use a crosstab query to get the display/outcome you want.
 

namliam

The Mailman - AWF VIP
Local time
Today, 10:34
Joined
Aug 11, 2003
Messages
11,696
No no no. we do not design tables with itterating columns... mon tue wed etc is an itteration of days/dates.... thus this is bad design.
Instead you should desing a table that simply holds the date and time worked, preferably in a TIME and/or datetime format.

Concantinating information seperating it with a comma, is a bad idea and an accident waiting to happen. Merging information will ineffitably lead to it needing to be split, which will find errors in data entry giving you headaches. I would suggest you split the CityState column into two columns.

Your Customer table should have a meaningless auto-increment ID column, I cannot see if you are using it, however if your CustNo is your PK of the customer table I suggest you add the ID column.

Futher we want to use some sort of naming convention

As a result your columnnames do not work, I suggest change them to
CustID
CustNo
CustName
CustCity
CustState

Then you have a second table:
CustID
WorkDate
StartTime or WorkedTime or whatever you need it to be

offcourse what you want to do is simple in a query even in flawed design, but this is going to give you headaches.
Code:
Select CustNo, sum(MON) as Mon, sum(Tue) as Tue, etc.
From YourTable
Group by CustNo
I expect you will want to add something like weeknumber to the Select and Group by

I hope this helps your project and perhaps a bit more.
 

Isaac

Lifelong Learner
Local time
Today, 02:34
Joined
Mar 14, 2017
Messages
8,738
Very strictly speaking you could normalize your data structure (although it's doubtful you'd ever have to add more days of the week, but good principle), but you could also come up with a dataset like this - then utilize it to populate a new table copy and go from there:

Code:
SELECT DISTINCT Table1.CustNo, Table1.cuname, Table1.citystate, (select top 1 [mon] from [table1] as [t] where [t].[custno]=[table1].[custno] and [t].[mon] is not null) AS xmon, (select top 1 [tue] from [table1] as [t] where [t].[custno]=[table1].[custno] and [t].[tue] is not null) AS xtue, (select top 1 [wed] from [table1] as [t] where [t].[custno]=[table1].[custno] and [t].[wed] is not null) AS xwed, (select top 1 [thu] from [table1] as [t] where [t].[custno]=[table1].[custno] and [t].[thu] is not null) AS xthu, (select top 1 [fri] from [table1] as [t] where [t].[custno]=[table1].[custno] and [t].[fri] is not null) AS xfri
FROM Table1;
 

Users who are viewing this thread

Top Bottom