Poor Design

[Imdabaum]

I'm working with a table that (IMO) is poorly designed. It has several inventory fields that can basically be any inventory. I think the database should have its own inventory table rather than entering 8 inventory items into the actual table.

However until I can change people's minds, I need a way of querying all the fields and displaying them as 1 field. I'm generating a report comparing inventories to their associated facility.

I have tried, "Select (Field1 + Field2 + Field3 + Field4 + Field5 + Field6 + Field7 + Field8) As InventoryItems From Table"

This was suggested on a SQL Server site, but doesn't quite get me the results I'm looking for. Can anyone help or point me into the right direction?

[Imdabaum]

I have tried, "Select (Field1 + Field2 + Field3 + Field4 + Field5 + Field6 + Field7 + Field8) As InventoryItems From Table"


Turns out this is on the right track. I just had the thought to try some VBA(&) concatenation rather than the typical (+) mentioned in the forum I read.

So if you ever need to do this in Access just replace the (+) sign with the (&)

Works like magic.

[CreganTur]

That's what I was going to tell you. Access uses an adapted version of Transact SQL, if I remember correctly, that leans more towards VBA methodologies than SQL standards. If something doesn't work the SQL way, then give the VBA version a try and you'll generally be right.

[Imdabaum]

That's the thought that occurred to me while I was flipping through options to make SQL compatible syntax.. If I can't make it compatible, make VBA compatible.

Now I'm stuck on the filtering criteria.
I have this as the purchase date of the inventory (again poor design, as different items could be purchased different days, but anyway)

I want to show all items purchased between two days. I've done a hypothetical join on a company, the purchase, and the manager responsible for maintaining the inventory. I don't know if posting the actual query would breach my security protocols. So I'm running with this example.

Tables: Company, Purchase, Manager
I've joined the company name with it's concatenated inventories and the manager who purchased the inventory, but am using the following to filter what items need to be purchased within the next 6 months.


SELECT [Comp].Company, [Purchases].NextRestock, Managers.MgrName, Managers.Limit, Managers.EffectiveDate, [Comp].[Job#],
IIf(IsNull([Inventory1]),"",[Inventory1] & ",") & IIf(IsNull([Cov_PublicRating]),"",[Cov_PublicRating] & ",") &
IIf(IsNull([Inventory3]),"",[Inventory3] & ",") & IIf(IsNull([Inventory4]),"",[Inventory4] & ",") &
IIf(IsNull([Inventory5]),"",[Inventory5] & ",") & IIf(IsNull([Inventory6]),"",[Inventory6]) AS Covenants
FROM (Comp INNER JOIN [Purchases] ON [Comp].[Job#] = [Purchases].[RR_Job#]) INNER JOIN Managers ON
[Purchases].[Risk Rater1]=Managers.MgrName
WHERE ((([Purchases].NextRestock)>Date() And ([Purchases].NextRestock)<DateAdd("m",6,Date()))
AND (([Comp].Inventory3) Is Not Null))
OR ((([Comp].Inventory4) Is Not Null)) OR ((([Comp].Inventory5) Is Not Null)) OR ((([Comp].Inventory6) Is Not Null))
OR ((([Comp].Inventory1) Is Not Null)) OR ((([Comp].Cov_PublicRating) Is Not Null)))
ORDER BY [Purchases].NextRestock DESC;

For some reason I still end up with dates in 2011 and well beyond the 6 month mark. Any ideas?

[CreganTur]

Do the tables you're using have established relationships?

[Imdabaum]

I'm googling for a solution to this, but in case anyone has already come across this. I'm not a fan of the way the report looks with that SQL and I don't think my manager is going to like that either.

I end up with a result of brocolli,,,, Is there an easy way to fix this without VBA? I can cycle through it in vba if that's my only option, but I'm always looking to better understand SQL tricks.