Help with query

[Brianmullen8]

I have set up a shift log in access, everything is running great but the query is taking too long to run or it will say it's run out of space.



I want the query to connect to 10 tables (machine) displaying the last date and issue with the machine. By default the query is very quick but displays the first entry in each table, I want it to display the last issue(last entry) .

When I set it to last or max the query will run out of space or crash. When I test this on tables with less entries it works well until the table starts to fill up.

Can anyone help or advise, I can supply a sample or screenshots if needed.

Thanks

[Bob Fitz]

Show us the SQL statement

[Brianmullen8]

Hi Here is the SQL statement, i have reduced the tables to 6 just to get it to work. If i put the 10 in it will not work

SELECT TOP 1 Last([175-5].Date) AS LastOfDate, Last([175-5].[Issues on Shift]) AS [LastOfIssues on Shift], Last([550-1].Date) AS LastOfDate1, Last([550-1].[Issues on Shift]) AS [LastOfIssues on Shift1], Last([200-4].Date) AS LastOfDate2, Last([200-4].[Issues on Shift]) AS [LastOfIssues on Shift2], Last([275-2].Date) AS LastOfDate3, Last([275-2].[Issues on Shift]) AS [LastOfIssues on Shift3], Last([200-10].Date) AS LastOfDate4, Last([200-10].[Issues on Shift]) AS [LastOfIssues on Shift4], Last([240-1].Date) AS LastOfDate5, Last([240-1].[Issues on Shift]) AS [LastOfIssues on Shift5]
FROM [175-5], [550-1], [200-4], [275-2], [200-10], [240-1];

[CJ_London]

you are using a Cartesian query - so the data set it is working on is likely to be massive. If there were just 10 records in each table, the dataset will be 10^6 long - 1m records. For 10 tables it would be 100m records, no wonder it won't work.

using numerical names for tables is also a bad idea - and Date is a reserved word and should not be used as a field name.

further, unless there is a very good reason why, you should have 1 table for all machines with a field to indicate your machine, not 10 tables, one for each machine.

And you need to clarify 'Last'. First and Last have no real meaning in databases without an order. 'Last' means the last record in the dataset - which is not necessarily the last record entered.

Suggest you need a union query to get the last/max record from each table - some thing like


SELECT "175-5" AS MachineName, Last([175-5].Date) AS LastOfDate, Last([175-5].[Issues on Shift]) AS [LastOfIssues on Shift] FROM [175-5]
UNION SELECT "550-1" AS MachineName, Last([550-1].Date) AS LastOfDate1, Last([550-1].[Issues on Shift]) AS [LastOfIssues on Shift1] FROM [550-1]
UNION SELECT "200-4".... etc

however I suspect your basic select is incorrect anyway

[Brianmullen8]

Hi Ajax,

The site i am at has 50+ machines split between 4 cells, I have set up a table for each machine for the engineers to log issues, this includes date, shift, name, product, cavitation, cycle and issues.

I want to be able to read the last entry for each machine within it's cell, this saves time going to each cell to read through logs and also allows reporting to pick out trends that cannot be seen looking through logs.

Would it be better having one table with all machines in a field?

Thanks

Brian

you are using a Cartesian query - so the data set it is working on is likely to be massive. If there were just 10 records in each table, the dataset will be 10^6 long - 1m records. For 10 tables it would be 100m records, no wonder it won't work.

using numerical names for tables is also a bad idea - and Date is a reserved word and should not be used as a field name.

further, unless there is a very good reason why, you should have 1 table for all machines with a field to indicate your machine, not 10 tables, one for each machine.

And you need to clarify 'Last'. First and Last have no real meaning in databases without an order. 'Last' means the last record in the dataset - which is not necessarily the last record entered.

Suggest you need a union query to get the last/max record from each table - some thing like


SELECT "175-5" AS MachineName, Last([175-5].Date) AS LastOfDate, Last([175-5].[Issues on Shift]) AS [LastOfIssues on Shift] FROM [175-5]
UNION SELECT "550-1" AS MachineName, Last([550-1].Date) AS LastOfDate1, Last([550-1].[Issues on Shift]) AS [LastOfIssues on Shift1] FROM [550-1]
UNION SELECT "200-4".... etc

however I suspect your basic select is incorrect anyway

[CJ_London]

Would it be better having one table with all machines in a field?

yes, actually two tables - something like

tblMachines
MachinePK autonumber
MachineName text
...
...

tblIssues
IssuePK autonumber
MachineFK link to tblmachines
IssueDate date
IssueOnShift text
...

then your query would simply be

SELECT MachineName, Last(IssueDate) as LastIssueDate, Last(IssueOnShift) as LastIssueOnShift
FROM tblMachines INNER JOIN tblIssues ON tblMachines.MachinePK=tblIssues.MachineFK
GROUP BY MachinePK

The way you have it you have built data into the structure of the db. If you add another machine, you need to add another table and them modify all your queries, forms and reports. The 'correct' way means you don't need to make any changes to the structure at all, just add another record to tblMachines.

Recommend you google 'normalisation' to understand how databases should be structured

[ssanfu]

You might check out https://www.accessforums.net/showthread.php?t=65906 Post #7 for some 'normalisation' sites.