I have a “little” problem that is driving me crazy. I’m creating a brand new application, small scale. So they will use Access for the job.
This application will control de mobile devices in the company.
Briefly, it has 5 main tables: devices, users, phones_numbers, rates and transactions.
Transactions is the table that combines all the other four. It is a historic table where I get all the information from.
I have to create a report listing all the users, the phone number and the devices related to that number.
Because, here’s the thing, a user could have a phone and a tablet, both tied to the same phone number. Doing a regular query, that would be two rows for the same user. But my bosses don’t want that. They want just one row which means that the values for the devices will have to be combined into just one field.
I found a solution doing a macro for this because Access only does PIVOT when using aggregated functions. But it runs really slow. For just 1000 lines, it takes 2-3 min.
Does anyone know how to speed things up? It is really getting me frustrated.
Sorry. As usual, I didn’t explain myself properly. I don’t need the number of devices a phone number has… I need to list them as if it was just one field.
Example.
In my query, I have the following result
name phone no. device
james 123456789 samsung phone
james 123456789 iPad
rose 555333222 iPhone
rose 444555444 iPad
rose 555333222 usb modem
rose 555333222 samsung tablet
But I need the result to look like this
name phone no device
james 123456789 samsung phone, iPad
rose 555333222 iPhone, usb modem, samsung tablet
rose 444555444 usb modem
SELECT t1.name
, t1.phone
, t1.device
+ IIf(t2.phone IS NULL,'',t2.phone)
+ IIf(t3.phone IS NULL,'',t3.phone)
+ IIf(t4.phone IS NULL,'',t4.phone) AS devices
FROM (((
transactions AS t1
LEFT OUTER
JOIN transactions AS t2
ON ( t2.name = t1.name
AND t2.phone = t1.phone
AND t2.device <> t1.device )
)
LEFT OUTER
JOIN transactions AS t3
ON ( t3.name = t1.name
AND t3.phone = t1.phone
AND t3.device <> t1.device
AND t3.device <> t2.device )
)
LEFT OUTER
JOIN transactions AS t42
ON ( t4.name = t1.name
AND t4.phone = t1.phone
AND t4.device <> t1.device
AND t4.device <> t2.device
AND t4.device <> t3.device )
)
I’m sorry to say that it didn’t work. For some reason, Access didn’t like the condition t3.device<>t2.device. It just said that the JOIN would not accept it.
I tried to work around it but…
Tomorrow will be another day
Thanks anyway. When I’ve searched for this, I only found answers in StackOverflow who simply said that you had to use a macro no matter what. But if I get this right, I think this will be much faster.
i haven’t used Access in about a decade, but i’m pretty sure i got the parentheses right
one way to proceed is to display the query in Design view, and see if you can get those ON conditions built in the grid at the bottom… then flip to SQL view and see if it fixes the parens to something it is happy with
I don’t think it has to do with the parens at all. I just removed the LEFT JOIN for t4 and remove the condition```
t3.device <> t2.device
And it worked. It didn't give the right result but it work. So it has something to do with that condition. I tried to show t2.device in the result, just in case that t3 couldn't read the value if it was not added to that result. But that didn't work.
I tried to use the design view and do an left joins (t2 to t1 and t3 to t2 the first time, and t2 with t1 adn t3 with t1 the second time). Then added the constrains (in design view you can't add those conditions like t2.device <>t1.device.
Again, the one that failed was t2.device<>t3.device.
And I tried to add the conditions on the WHERE clause. But, of course, that gave a completely different result and most of the records where not included.
Also, if I just the left joins, with no other conditions but equal names and phones, for some reason, the values for t2 and t3 phones aren't null when they should. It is more like all the possible combinations with the device values availables.
But I'm sure that there's a form to do this.
