Dropdown option in query?

[MsNaniB]

Hello! I have another thread going with another issue so I thought I'd start a new one for my new issue
My boss asked me to build the following queries:

Total Spend by Vendor
Total Spend by Year
Total Spend by Group
Total Spend by Account
Monthly Spend by Vendor
Monthly spend by month
Monthly spend by group

Well now he's come to me today and wants to know if all of that info can be placed into just one query, and can drop down boxes be put in so that he can pull out all of that info by using drop downs, instead of having me write 7 queries? Sometimes he'll want all of the info above, and sometimes he'll just want all of that info for one vendor, or one month, or one group, etc. I'm brand new at this so I have no idea how to make this happen for him. Any help would be most appreciated!
Thank you,
Nani

 

[speakers_86]

He seems to be asking a lot of you if your new to this. How the db functions should be of no concern to him. He only needs it to function as he expects. If your the developer, you need to understand what it is he wants, and how to go about creating it. Just create the reports however gets it done, and create a form that with the drop down box that lists said reports. I'm sure that is what your boss is looking for. As for selecting only one month or one vendor, in the form, you will need more drop downs where these parameters would be selected. Then in the button that opens whatever report is select, it will need to look at the other two combo boxes for month/vendor parameters. This can be done, but it will probably be hard for you.

 

[MsNaniB]

Hi there Speakers,

Thank you for your response. We have a lot of vendors that are tied to our financial stuff. So we have a budget table, a vendors table, an actuals table, etc. He wants to be able to open a drop-down menu (in a query) and pick whichever Vendor he wants, for whatever month he wants, and to see if we're over budget for a certain month, etc.
So is there a way to put all this info into drop down menus in a query? We do not have forms, by the way.

Thank you,
Nani

 

[speakers_86]

Drop downs and queries don't go together. So if you do have a drop down, it sounds like it is probably at the table level. This is called a lookup query. These are frowned upon, and should be avoided. If you don't have forms, then you are probably entering your data directly into a table, which is also frowned upon.

No offense, but maybe you should find someone more capable to complete this project for you, especially if other people rely on the database.

Additionally, if you actually don't have any forms, then I would expect some serious design issues, and that your db is probably not split. If multiple people are using your database effectively, then it is more likely that you do have forms with datasheet views, and hopefully the db is normalized and split.

 

[MsNaniB]

What about inserting the look-up column? I tried it and I was able to look up a vendor's ID but it's not bringing up anything else that has to do with the vendor, so I need to connect it to all of it's information and bring it up somehow. (I have 12 relational tables linked together)
We have no forms and I have one week to make this one function happen for my boss. It's too late to build forms and combo boxes and all that and I don't have that skill level.
So if anybody can help me, I'd appreciate it.
Thanks,
Nani

 

[speakers_86]

I guess you missed the part where I said lookups are bad.

 

[MsNaniB]

lol...No, I didn't miss it... but I don't see why I can't use it if the function is there?

 

[speakers_86]

If you don't even have any forms, you have larger issues at hand.

 

[MsNaniB]

Well, I was asked to build queries and reports..that's it.