Solved Combine Records

[RevJeff]

Hello all,

I have a table that looks like this:

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.

Thanks for any help you can offer.

 

[theDBguy]

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]

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.

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]

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:

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;