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

gojets1721

Registered User.
Local time
Yesterday, 21:29
Joined
Jun 11, 2019
Messages
429
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

Super Moderator
Staff member
Local time
Today, 00:29
Joined
Jan 23, 2006
Messages
15,352
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

Registered User.
Local time
Yesterday, 21:29
Joined
Jun 11, 2019
Messages
429
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
 

gojets1721

Registered User.
Local time
Yesterday, 21:29
Joined
Jun 11, 2019
Messages
429
Sorry didn't realize that wasn't allowed. I'll try to delete the other one
 

Micron

AWF VIP
Local time
Today, 00:29
Joined
Oct 20, 2018
Messages
3,476
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

I’m here to help
Staff member
Local time
Yesterday, 21:29
Joined
Oct 29, 2018
Messages
21,322
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

Registered User.
Local time
Yesterday, 21:29
Joined
Jun 11, 2019
Messages
429
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

Registered User.
Local time
Yesterday, 21:29
Joined
Jun 11, 2019
Messages
429
And I can't post the crosspost link, as I haven't posted to required 10 posts yet
 

jdraw

Super Moderator
Staff member
Local time
Today, 00:29
Joined
Jan 23, 2006
Messages
15,352
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

Super Moderator
Staff member
Local time
Today, 00:29
Joined
Jan 23, 2006
Messages
15,352
You can attach if you make it ZIP file.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:29
Joined
Oct 29, 2018
Messages
21,322
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

Super Moderator
Staff member
Local time
Today, 04:29
Joined
Feb 19, 2013
Messages
16,521
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

Registered User.
Local time
Yesterday, 21:29
Joined
Jun 11, 2019
Messages
429
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

I’m here to help
Staff member
Local time
Yesterday, 21:29
Joined
Oct 29, 2018
Messages
21,322
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

Super Moderator
Staff member
Local time
Today, 04:29
Joined
Feb 19, 2013
Messages
16,521
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

Registered User.
Local time
Yesterday, 21:29
Joined
Jun 11, 2019
Messages
429
I did check out the link, but I'm not sure how that applies to running a universal report from various IDs
 

CJ_London

Super Moderator
Staff member
Local time
Today, 04:29
Joined
Feb 19, 2013
Messages
16,521
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

Registered User.
Local time
Yesterday, 21:29
Joined
Jun 11, 2019
Messages
429
So is there a way that after selecting those entries, you can run a report that shows them?
 

Users who are viewing this thread

Top Bottom