Crazy Query

[businesshippieRH]

I have a series of queries to get all of the information I'm looking for. Eventually, all of this information will go into a crosstab to show earnings across multiple jobs for each piece of equipment. However, I've hit a stumbling block.

I need to be able to do a dlookup using a result from a query to make the final query before crosstab.

I have a table whose sole purpose is to let access know which "RateSchedule" to use for a particular project. It has two columns: ID and RateScheduleTable. When a new RateScheduleTable is created, it adds the name of the newly created table to this table and gives it an autokey. These autokeys are then updated in my projects table so that the project is tied to a specific rate schedule.
For example (see below screenshot): 12, tbl_RateSchedule_Test

My current query pulls these names and puts it next to the transaction in question. The screenshot below shows a bit more of what I'm talking about:


I need a way to use these table names as a parameter in a dlookup in the next query to complete the information I need before making the crosstab. The bits I need to "pull" are highlighted in red.
Eg: Dlookup(DailyRate, tbl_RateSchedule_Test, Query4.EqTypeID=tbl_RateSchedule_Test.EqTypeID)

Thanks in advance! There may be a better way to do this too... I'm open to suggestion.

 

[CJ_London]

sounds like you have not normalised your data. If you don't normalise it, you will, as you have discovered, have great difficulty in doing certain things.

You should only have one rateschedule table, by the sounds of it with one extra column being the identifier which you are currently using within the table name.

 

[businesshippieRH]

I agree, a single table with rate schedules would be nice. Unfortunately, due to upper level decisions, each rate schedule must be its own table.

My relations look something like the attached picture (drawn in excel for example). They are all tied together using PKs (*in image) in their respective tables. However, the rates must be kept as separate tables (once again, not my choice).



Thanks again!

 

[jdraw]

I would like you to describe a typical day in the business this proposed database will support. We need to understand the business and related business rues/facts if we are to provide focused suggestions/advice.

Management typically tells you WHAT they would like. It's your analytical skills that puts the "would likes" into a set of requirements, and once agreed to, then to some design options.

So far, in my view, we haven't heard a business rationale.
I think management, or someone, has described 1 type of report they would like. But as usual, the devil is in the details.

 

[CJ_London]

it would be more helpful to understand if you show your relationships as they actually are in the db, rather than a layout in excel which may or may not be relevant.

I guess you could try something like

myRate:Dlookup("DailyRate", RateScheduleTable,"EqTypeID=" & EqTypeID)

in a query which uses the query you attached linked to query4

No idea if it will work and if it does, hope you don't have too many rows - it will be painfully slow

 

[spikepl]

Unfortunately, due to upper level decisions, each rate schedule must be its own table.

I suspect we are dealing here with the usual Excel-inspired confusion.

In Excel, data input/output/storage is all the same and what you see in a worksheet.

In Access, storage is unrelated to input and display.

I cannot imagine why management would want to interfere in the internal workings of a database. But I could imagine them mandating what input data or presentation looks like.

So what is stopping you reading separate external rate tables into the same internal normalized Access table, displaying the rates as desired by the higher powers, but internally using and processing the sensible one common table?

 

[businesshippieRH]

spkiepl: Thank you. You're absolutely right.

To answer some questions: We are a small environmental consulting firm. We deal with a lot of data, but nobody really manages it beyond the scope of a project at a time... Technically, I'm a biologist. I just have the strongest computer background of anybody in the office (mostly self-taught). Hence: my problem.

The "powers that be" have used excel sheets to create job proposals for years, and the rates are established for each job. We're trying to move to a "default", but there are times where it's useful to tweak equipment costs vs. personnel costs, travel costs, etc. And: They're very hesitant to give up any control on this process. (Translate: telling them they have to use a standardized form to input it will be enough of a battle).

I'm thanking spikepl the most because he's absolutely right, I got stuck on having a way to import their individual tables rather than normalizing data as all of you suggested. I'll be going back and reworking the input process so that their individual tables will populate a single table, and solve the real root of this problem rather than trying to use a band-aid.

Thank you all for your advice.