I am updating a DB thats was written a few years ago in Access 2007. I am using 2010 and have transferred a number of items over and have redesigned all the reports. Everything works perfectly except for 1 query.
The data is created by a simulation and output as a text delimited CSV, this is then imported into the DB via a macro. Each row of data contains 10 fields of which 4 contain the data on which all the reports are based on.
I have attached a stripped down version of the DB and a sample copy of the data CSV (this is already in the DB).
The 4 fields concerned are Loco1, Loco2, Loco3 & mileage and the query that is causing me the headache is Trebleheaders.
On each row Loco1 will always have data be it alpha or numeric and mileage may or may not have a value.
On rows where Loco1 & Loco2 have data mileage will always have data
On rows where Loco1,2 & 3 have data again mileage will alway have data
Rows where only loco1 and rows where 1 & 2 have data everything works as it should and produces the right result.
Rows which have 1, 2 & 3 containing data, the query only produces 2 of the 3 expected rows and i cant figure out how to get the third. I can see the process in VBA how it achieves the 2 results and have tried various ways to get the third but to no avail.
If you look in the Input moves table which is where the macro imports the data into after running the queries hopefully it will make the next sentence clearer.
Any row that has data in Loco1, Loco2 & Loco3 needs to have 2 extra rows produced so that the data in 2 & 3 from the intial row end up in Loco1 in each row. It doesnt matter which way round 2 & 3 are in the 2 extra rows, as its only Loco1 that provides the mileages data for all the reports
Hope this makes sense to someone and sorry about the length of post
My knowledge of Access is limited but doing this has refreshed a few things and i've learnt a few new things
The data is created by a simulation and output as a text delimited CSV, this is then imported into the DB via a macro. Each row of data contains 10 fields of which 4 contain the data on which all the reports are based on.
I have attached a stripped down version of the DB and a sample copy of the data CSV (this is already in the DB).
The 4 fields concerned are Loco1, Loco2, Loco3 & mileage and the query that is causing me the headache is Trebleheaders.
On each row Loco1 will always have data be it alpha or numeric and mileage may or may not have a value.
On rows where Loco1 & Loco2 have data mileage will always have data
On rows where Loco1,2 & 3 have data again mileage will alway have data
Rows where only loco1 and rows where 1 & 2 have data everything works as it should and produces the right result.
Rows which have 1, 2 & 3 containing data, the query only produces 2 of the 3 expected rows and i cant figure out how to get the third. I can see the process in VBA how it achieves the 2 results and have tried various ways to get the third but to no avail.
If you look in the Input moves table which is where the macro imports the data into after running the queries hopefully it will make the next sentence clearer.
Any row that has data in Loco1, Loco2 & Loco3 needs to have 2 extra rows produced so that the data in 2 & 3 from the intial row end up in Loco1 in each row. It doesnt matter which way round 2 & 3 are in the 2 extra rows, as its only Loco1 that provides the mileages data for all the reports
Hope this makes sense to someone and sorry about the length of post
My knowledge of Access is limited but doing this has refreshed a few things and i've learnt a few new things
