Report that prompts ID number, and then creates report based on that ID(s)

[gojets1721]

So basically I have a table with thousands of events (complaints, injuries, compliments, etc.) for our retail stores.

I'm looking to create a report with a standardized format that shows the events that a user wants. So hypothetically, the user would:
- write down the events that they want a report on (let's say ID 454, 786, and 908)
- click the report; it would prompt the user to type in the IDs the report needs to show, and the user would type them in and press enter
- The report would generate a document that has a standardized format that allows the user to print it / save it / etc.

Does anyone have a walkthrough guide or link to video of how to do this? Or is able to walk me through this?

Thanks!!!

 

[jdraw]

Reporting is done from the database.You haven't told us anything about your database -the tables, relationships....
Here is a link to several articles on Database Planning and Design that may be helpful.

Having to manually find and record/write down IDs seems to be a non-optimal approach, but we need more info if we are to offer more focused responses.

 

[gojets1721]

It's a rather simple table. There are 8 columns in the table:

- Access generated ID
- Date & time of event
- Submitted by
- Event Type (complaint, injury, etc.)
- Location
- Employee(s) involved
- Description
- Follow-up

There's no relationships set up. Then, I have 1 form that managers use to add to that "Follow-up" column. And yea, certainly the plan is make finding the events someone is looking for a bit easier, but ultimately for now, I think just generating the report by inputting the ID will be greatly beneficial. If you have a streamlined approach, by all means

 

[jdraw]

You might get some ideas from Martin Green's Dynamic Reports.

 

[Deleted member 147267]

cross posted
https://www.accessforums.net/showthread.php?t=77224

 

[gojets1721]

Sorry didn't realize that wasn't allowed. I'll try to delete the other one

 

[Deleted member 147267]

Sorry didn't realize that wasn't allowed. I'll try to delete the other one

As noted there, it's allowed. Declaring it with a link to the other post (both ways) is acceptable practice I think. However, I'm not going to extrapolate on the whole subject. Mr Excel called me out for posting the usual link on the etiquette of this topic so I don't any more. I don't go there either.


Maybe just modify your posts with links?

 

[theDBguy]

It's a rather simple table. There are 8 columns in the table:

- Access generated ID
- Date & time of event
- Submitted by
- Event Type (complaint, injury, etc.)
- Location
- Employee(s) involved
- Description
- Follow-up

There's no relationships set up. Then, I have 1 form that managers use to add to that "Follow-up" column. And yea, certainly the plan is make finding the events someone is looking for a bit easier, but ultimately for now, I think just generating the report by inputting the ID will be greatly beneficial. If you have a streamlined approach, by all means

Hi. Let's say you have a report based on the table data you want to show and you're able to filter it by the ID number. One way to do it is to create a parameter query where it asks/prompts the user to enter the ID numbers. The result of the query should then be filtered to those entered ID numbers. You can then use this query for your report.

 

[gojets1721]

That's what we do now, but the issue is the reports are never standardized. Basically when a manager does this, they create a new report and it's always different than one before and after it.

Is there a way that this query could flow into a pre-set report template?

 

[gojets1721]

And I can't post the crosspost link, as I haven't posted to required 10 posts yet

 

[jdraw]

Can you describe what exactly this means (examples please)

it's always different than one before and after

.

What specifically is the same from one to another?
You can have several "reports" that could have parameters for Dates, Employee, Customer..., but something has to be consistent to call it a template.

 

[jdraw]

You can attach if you make it ZIP file.

 

[theDBguy]

That's what we do now, but the issue is the reports are never standardized. Basically when a manager does this, they create a new report and it's always different than one before and after it.

Is there a way that this query could flow into a pre-set report template?

Ah, okay, so I think you're asking how to make a dynamic report. Yes, this is possible but will take a lot of work to set up. I think there may be some demos available for this. Let me take a look and I'll let you know if I find any.

 

[CJ_London]

seems to me you probably need more than one table

- Access generated ID
- Date & time of event
- Submitted by
- Event Type (complaint, injury, etc.)
- Location
- Employee(s) involved
- Description
- Follow-up

items in green - probably just a lookup table, Item in orange requiring a many to many table and the one in red is rather vague - do you just mean one of your stores? or a location within a store?

And how do you expect someone to remember the access generated iD's? Do they do some sort of search? - could take some time if there are thousands of records.

If your users are looking through a list, you might find this link of interest https://www.access-programmers.co.uk/forums/showthread.php?t=289116

 

[gojets1721]

So locations refers to our stores. We have 18 stores so it could 1 of 18. And we have a form where users look at an event in its entirely. So basically users "control+f" and type in an employee's name (to see all the events associated with one employee) and then write down the IDs associated. Primitive, I know, but streamlining that is for another day.

In regards to the reports being different, users will run a query and then run their own report and they need manipulate all the cells in order to make it printer friendly and it's a hassle and never the same. It'd be nice to just have a pre-set template (if possible) where users can just always use that and not have to manipulate a new report every time.

 

[theDBguy]

In regards to the reports being different, users will run a query and then run their own report and they need manipulate all the cells in order to make it printer friendly and it's a hassle and never the same. It'd be nice to just have a pre-set template (if possible) where users can just always use that and not have to manipulate a new report every time.

Hi. The closest example I could find is this one. Hopefully, you can take a look at this and be able to apply the same technique to what you're doing. Good luck!

 

[CJ_London]

So basically users "control+f" and type in an employee's name (to see all the events associated with one employee)

and if the record has been completed with a different name spelling? or two people have the same name?

so why not just have a combobox for the user to select a name, then a simple criteria applied to the openreport command?

did you look at the link I provided?

 

[gojets1721]

I did check out the link, but I'm not sure how that applies to running a universal report from various IDs

 

[CJ_London]

I'm not sure how that applies to running a universal report from various IDs

it provides a way for a user to select the ID's they want to report on - rather than writing them down, then rentering them when they want to run the report

 

[gojets1721]

So is there a way that after selecting those entries, you can run a report that shows them?