Dynamic Queries providing detail and creating total records 1

[surfside1]

I have a need to allow our employees to create a query that will capture various selections from a form. They like to slice and dice data and view it different ways, at a detail level or total level or both. All tables are the same with identical field names. I know this will be in VBA using variables, and I'm hoping to dynamically create the query on the fly. Has anyone done this before?

Thanks

 

[fneily]

All tables are the same with identical field names."?????
Did you NORMALIZE your tables? It doesn't sound right.
Please supply table structures, more DETAIL on what you want, etc.
Do you want a form with a bunch of dropdowns, option groups, etc. so they basically create their "own" report?
Example, I create a form that allows users to input a date range or quarterly, then check the type of report they want - detail, totals. Then when they click a button, behind the scenes, depending on their selections, a filter is created for the report.
You need to supply more info on your wants.

 

[surfside1]

The table names are uniquelocationname. I imported them from XLS where they all had the same column names. Since in VBA the code is tablename.field I thought I would be just referencing the fields with tablename.field and that would make them unique. Also I could use the same report and only reference which table it would use.

Example
Location Commodity Supplier
location name commodity name supplier name

Appreciate your help.

 

[fneily]

You must have duplicate data all over the place. eg. commodity name - let's say orange, it should only appear once; supplier name should appear only once. You're not using Access' relational abilities the right way.
But since the damage is done, you still haven't told me what you EXACTLY want.
Do you want dropdown boxes for LocationName, CommodityName, and SupplierName?
Are there any other fields?
What should the reports look like?
Details are important for an answer.

 

[stix4t2]

Maybe if you show us some sample data from a couple of tables that would help. Just select the top one or two records from the table and paste them into a posting. The paste will show us column names and sample data. Give it a try.

 

[surfside1]

I'm now thinking that I need to create one big table, it would have 28K records, with the following fields:
ID
Location
Commodity
Supplier
Supplier_COO
LCC
2005_Total_Spend
Analysis_Percent_Commodity
.....(and more)

They all have Location that would identify which facility it belonged to. You can tell I'm new at this, and I don't think there is really any relationship between each table, they have their own data. Appreciate the help.

 

[fneily]

From what you present, I see four tables - a supplier table with their info, a commodity table with their info, and a Location table with that info. The last table would be a junction table to connect everything. It would possibly look like this:
JuncID LocationID commodityID supplierID 2005_spend Ana_%_c

The junction table would keep all info that's related to all the rest of the tables.

Remember, in a RELATIONAL database, you should not have extraneous info such as supplier name, address, etc. copies.
Looks like you're going to be doing alot of typing.

Just a thought.

 

[surfside1]

I wish I could be in one of your classes, Instructor.

I agree with the different fields that should be in other tables, just didn't know if having a large table would a consequence I'd pay. Can I relate the key assign by Access?

"Today" is to satisfy the powers that be. I added all tables into one. Can you add data to a table, like cut and paste (thru Excel import)?

But..... would the code you are talking about, be easier? These folks only want to use Excel and (a few of us) are trying to get them to Access (we know the benefits), they need to also.

The code you are talking about, in what context would it be used?

Appreciate your help!

 

[stix4t2]

Surfside,
One of the most important parts before development is the user buyin. Are you thinking of trying to maintain this stuff in both Access and Excel. How many excel files are there? Do they all have the exact same columns? Are they all in the same folder?

 

[surfside1]

Just to give you some background, my concern has always been that this company is ingrained in Excel. I am trying to get whatever powers that be, to think about Access instead of recreating a worksheet for every request to slice and dice the same data which is extracted from their main data warehouse (apparently they don't provide that feature). It's in a crunch timeframe, and most of the time folks put them in a different Spreadsheet. I think it is easier in Access to produce the results of the same data that they import from their data warehouse when they need it. This data we are working on is a one time update of the data and is provided because it is for the previous year, but throughout the year requests come in different views of the data, and it is crunch time.

I know what you are thinking, this is totally unplanned and you are right. I have no control over that. I plan to make sure the columns will always be the same unless major changes are needed in the format, and it would apply for all 80 facilities all around the world, and I think I can help them do this. I know they have a comfort zone with Excel but if the Access approach is intuitive, plus they have the software anyway, it just needs some communication.

My plan is to make sure when this data is needed, I'll give them a spreadsheet template if they don't use Access. I would also make sure I can output the Access results in Excel. So I'm hoping to go back and forth.

Long winded, I apoligize, and it is not your problem, but I need to know if I'm trying to accomplish something that is impossible. Thanks for your time.

 

[stix4t2]

Hey Surfside,

Your efforts are admorable. Excel can be a real hassel in unmanaged data in seperate places. Access can verify input, create intuitive entry forms, offer selectable values, and just make life easier.

But I got to say, you are setting yourself up for possible failure. Without a vision and on short time, you may cause more damage than good. Access apps that don't work, or have problems will reinforce their beliefs that Excel is the only way.

You will be able to sync data using replication or not, but good planning will ensure positive results. My recommendation to you is start small. Can you start by working with one user. Build the Excel implementation into an Access app to design and implement atleast the data entry part. Find a user that wants to try this new approach so you're on friendly ground. If you make that user happy, they will tell two friends, and so on, and so on. Then as a second phase work on the sync part.

 

[surfside1]

Stix,
Thanks and I hear what you are saying. I've been intending on talking to a particular person (user of the system), and have been told to "just do it and don't bother them", but my experience says not.

I'm with you.
The replication problems would entail "what" as an example.

Thanks for your time!