Produce report after appending records?

[Astron2012]

Hi, I have a database where we regulary import excel data to generated from a form sent to our clients. The excel data that we import normally holds around 10 to 40 records at a time.



The current process I use to do this is to run a macro that creates a new table (tblImportForm), and then run an append query to append those records to our existing main table (tblJobDetails). I should also mention that the macro first deletes the existing tblImportForm before creating a new one with the same name with the new data.

I have used this method rather than appending the data straight into the existing tblJobDetails as I found I came into more problems with generating IDs etc.

What I want to do is have a report come up after the data has been imported/appended, that lists the new data imported with the new record IDs generated in the main table tblJobDetails.

It would be easy to do this is if I only needed the data or could use the IDs in the first table I import to, but the idea of the report is to give the user the new IDs (PrimeKeys) from the second table that the data is appended to.

I could maybe do a count of records in tblImportForm and then produce a report using a query from tblJobDetails that pulls that number (the count) of data from the last record backwards? But I don't know exactly how to do this.

Or maybe this can be done a better way?

Any help would be really appreciated

[jzwp11]

Since you are doing this on a regular basis, I would recommend adding a date field to tblJobDetails (i.e. dteAppended) and set it's default value to date() or now(). Then you can use that date field as part of the criteria for the query on which the report is based.

[Astron2012]

Hi,

Thanks for your suggestion, I could try using now(), I didn't want to use date() because there will be other records added that day that I don't want appearing on the report. I will give it a go.

Cheers

[Astron2012]

OK I tried that but the problem is that even with setting the default value to Now() it only puts this value into the last record I uploaded, but the other record's "Date Entered" fields are blank.

Any ideas??

[jzwp11]

All new records added since you put in the field should now have a date. All records that existed prior to adding the field will have no value in the date field (date field is null). You could run an update query to set the date field to some previous date or you can just use the fact that those records whose date entered field is Null should be ignored.

I didn't want to use date() because there will be other records added that day that I don't want appearing on the report.

You did not mention the above. I assume these other new records are not begin added via the import/append method you mentioned. In your report, if you want only those records that were added via the append query, then you cannot use the default value approach I mentioned. I would recommend getting rid of the default value for the dateentered field. You can populate the dateentered value using your append query by setting a constant field to today's date. That append query might look like this

INSERT INTO tblJobDetails ( field1,field2, etc...., dateEntered)
SELECT tblImportForm.field1, tblImportForm.field2 etc..., date()
FROM tblImportForm

[Astron2012]

Hi, I just tried the imports again and this time it did add the timestamp to all the records not just the last.

So now working, GREAT!!!!!

Thanks so much

[jzwp11]

...the report that is based on that same query does not show the records.

If the query is working and shows the correct information but the report does not, it points to a problem with the report. Check the query that the report is actually using (i.e. the report's record source) and verify that the dateEntered field is present as well as the criteria necessary to filter the data.

[Astron2012]

OK PROBLEM.....

I tried the report on my own version and it now works fine, BUT when put onto the network the report doesn't work because the network is slow, so it is too late by the time it runs the Now() report

So I don't think it will work using Now() or Date() as you can't predict how long it will take for the report to run, sometimes it is quick, other times it can take up to 20 seconds.

This is why I really think the best option would be to run a query to get the count of records in tblImportForm (which I have already done) and then run another query that uses that COUNT figure and subtracts that from the last record in tblJobDetails (main table) and shows all the records in between. But I have no clue how to do this as I am not great with SQL.

Any help on this would be great!

[Astron2012]

Also, I should point out that the macro that runs the report first has to import the data (transfer spreadsheet), then run 2 other queries to add to the data and append to the main table, then open the form to show the last record before I can run the report with the Now() query.

[jzwp11]

...from the last record in tblJobDetails (main table) and shows all the records in between. But I have no clue how to do this as I am not great with SQL.

In order to find the last record that was added to a table, you need a date/time stamp field and you can use a simple aggregate query to get that

Select Max(timestampfield)
from tblJobDetails

Alternatively, if you are using an autonumber primary key field in the table, you could find the max of that field as well.

I tried the report on my own version and it now works fine, BUT when put onto the network the report doesn't work because the network is slow, so it is too late by the time it runs the Now() report

The only way it can be too late is if others are importing & appending records, so if that is the case, there would be times when the number of records in tblJobDetails can change between when the records are appended and when the counting queries are executed by the various users. Can the same user conduct multiple imports throughout the day? If so, do you need to distinguish the records added during one event by that user versus a second time they might import records? If you have multiple users, you might need to define a user field in addition to the date field.

[Astron2012]

I have multiple users that add one record at a time. I have another user that imports maybe 1 to 3 spreadsheets each day. The report needs to show the records just added in that particular upload, not other uploads she has done that day, so a username won't work. Unfortunately there is no other field that could be used to search on for the import, I initially thought of this.

I'm not too concerned with another user adding a record at the same time as this is avoided.

However to run a query on the Now() function only works if the data is imported and the query is run at EXACTLY the same time to the second. With the network being slow, there is a slight delay on occasions that means the query has "missed the boat" so to speak, and the data is imported first, then 2 other queries have to be run BEFORE the final query that we are referring to.

I have thought of another possibility though. Maybe I could run the last query based on Now()>Now()+30 seconds

I have tried Now()>DateAdd("s",-30,Now()) but this is not working and it brings up all records.

Maybe I just need help with the correct syntax?

[Astron2012]

OK Think I have sussed it... I just do >DateAdd("s",-30,Now()) in the field not Now()>DateAdd("s",-30,Now())

This seems to be working, now to test on the network...

[jzwp11]

Good luck; let us know how it works out.

[Astron2012]

Yep all working now, thanks for your help

[jzwp11]

You're welcome. Glad to hear that it is all sorted out.