Dumb (probably) Questions...

[Daffyduck]

So, I said I'd help a friend sort out some materials she's been recording on her work as a theatrical costumer for some serious folks for the past 10 years.

Apparently she took a course a while ago and learned how to set up the basic tables she needed and has been duly entering info into them ever since.

She's basically trying to figure out if her pricing and her hours are working out well, and she asked me to put four tables (really queries) together. A couple need crosstab queries, and I figured those out, but then they need columns added after those results, and sums and multiplications after that, and that's where I'm getting stuck.

I did everything in Excel and after tweaking a few things, it worked, so I have some basis for knowing if I'm on the right track, but I'm foundering on the stupidest things...at least it seems so to me.

And somehow I can't find any friends who know this stuff who could look at it and tell me what's wrong! Anyway:

1) One issue is that I seem not to be getting the relationships set up correctly, and I can't seem to find any instructions for how to think about those more logically. I've read through a couple of books, but nothing explains how to decide what lines to draw to get what results....or has a parallel I could follow that matches what I'm trying to do.

2) The other is that I'm getting the dread "Type mismatch in expression" message and I'm not seeing where it's coming from...I've looked at a couple threads here but so far no solutions have popped up; possibly this is related to No. 1 above, or it could be a different problem, just can't tell.

There may be other problems with what I'm trying to do, ongoing, but I have a feeling if those could be fixed, I could at least see what they'd be.

Any help, suggestions, appreciated.

 

[Beetle]

We can't give you advice on how to set up your relationships without knowing more about your application and the nature of what you're trying to model.

Ditto for the Type Mismatch error. Hard to say without knowing the circumstances under which the error occurs.

If you want to post a copy of the db here we can take a look. If you decide to do that you'll need to zip it first.

 

[Daffyduck]

Hmmm, OK, it's got a bunch of false starts in it...I'll see if I can clean it up and zip it to send.

I'm also getting a series of things where I get the same answer all the way down the column, I get the project names and fees/hr OK from one query but when I try to add in the total hours, so I can just multiply to get the total paid, I get the same number of hours all the way down. I tried adding the project name from that table again, and it just showed "1"s all the way down.

It's just frustrating; I know what to do to get a one-time answer in Excel but I understand that she wants to be able to plug in numbers and keep getting results ongoing...I expected more crossover transference, I guess. (i.e., I know pivot tables, so crosstab queries made sense, but this other stuff is just...weird!)

Anyway, OK, I'll get to work on sending something.

 

[Daffyduck]

Digging further, I"m also noticing that the column for "Projects," which is usually a name (so, text, I would think) is entered as a number in the file type.

Is that an error or is there some reason for doing it that way that I just don't know enough to recognize? (and so, should I change it or leave it be?)

 

[Daffyduck]

Um....I don't see the "manage attachments" button that I'm supposed to see to be able to upload a file...per the FAQ instructions, anyway...if I survive the learning curve on the forum software, Access will be easy....!

 

[Beetle]

If you're using Quick Reply, you need to click the Go Advanced button, then scroll down you should see the Manage Attachments button.

 

[Daffyduck]

So, QryHpJvF3 gives me the project name and the hourly rate for all those projects that were quoted on an hourly basis.

Good enough.

But when I try to pull the total hours for those projects from the Hours_crosstab query, it doesn't work....that's when I get the first name repeated all down the column, and the first total hours likewise, in the next column.

If I try linking the project name in QryHpJvF3 to anything in Hours_crosstab, it won't let me (dread mismatch message again).

But none of that can matter because there is no way to upload the files, apparently.

<<silent scream>>

I've spent the last 6 hours on this, and am no further ahead.

I've also noticed that at least 20 good folks have strolled past the thread and only one good Samaritan has stopped by to help.

Am I in the wrong place? I've noticed there are other Access boards but I thought I'd start with this because it was tied to MSW in some way, so figured it was the main one.

Not being snarky, just trying to figure this stuff out.

 

[Daffyduck]

It seems quite happy to give me many silly icons to choose from, but none of them seems to be about managing a file or attaching it.

Oh, wait, maybe one is a paperclip...OK, finally...I think it worked.

I attached the Excel file as well, since it tells the story better than all my cheeps, bleeps and squawks.

I don't see icons for the files once I closed the uploader, so we'll see if this worked...

 

[Daffyduck]

Guess it did.

So, there are four tables (queries, really) I'm trying to set up.

1) One is "hours per job vs. fee" so:
Project
Fee
Hours: (7 stages as a X-tab) then
Fee/hour
with an Average line to be figured at the bottom.

That's the one that's been the balkiest, since adding any other columns just doesn't seem to work.

2) The Total Hours worked is just a x-tab by month, with totals to be divided by 52 to get hours per week.

Adding the last column didn't seem possible with the x-tab so I created a new query, took the columns from that one and tried to do the figuring in columns I added after that. Still fiddling with it.

3) Another is on the duration of projects, so
Project
Start of Work
End of Work
Duration
Hours
Hours/week

I figured out how to get the dates to translate and subtract for the duration.
Adding Hours and Hours per week didn't work...again, it claimed a mismatch even after trying to tie the tables together several different ways.

And since I did it all manually in Excel, I have to believe it's possible here and there are just some techniques I'm missing to do it, but since "no system knows what it doesn't know" I don't know what those are.

Again, thanks for any help...very much!

 

[Daffyduck]

So, is this dead in the water?

I'm noticing lots of lookers but few comments.

I really do need the help...I don't see a PM system here, if it's more complex and not all that useful to others, I'm glad to take it offline as well.

 

[catalin.petrut]

Maybe it looks dead becouse is a lil complicated for me to understand what you need the db to do. I've looked at the excel and there are lots of calculations and for this you dont need a db. If u would like to take this outside the forum, my email is the same as the user, on gmail.

 

[Daffyduck]

Thanks, appreciated.

 

[Daffyduck]

This is the group of tables with my comments on each one, zipped and attached.

Having mocked them, one of the icons does indeed make sense here:

:banghead::banghead::banghead::banghead:

One for each table I'm trying to do...

:--}

DD

 

[catalin.petrut]

I have atached a simple db that contains the necesary infos and a form to insert more. The others info from the excel file (the first 2 sheets) dont need to be inserted into the db. That info can be obtain from queries. I hope you can create some queries that will help you view the necesary info.

 

[Daffyduck]

Thanks very much!

I've opened it and will look it over.

Much appreciated.

 

[Daffyduck]

Uh-oh, I'm in Access 2000, and don't have access (oooh, sorry) to anything later.

A search for conversion options online shows that they only want you to convert in favor of the upgrade (of course)...I'll poke around here to see if there's a way to do this, unless some kind soul wants to tell me directly...

Thanks--DD

 

[catalin.petrut]

Here it is a db saved as 2000 version

 

[Daffyduck]

Thanks and blessings!

DD

 

[Daffyduck]

I didn't get a chance to work on it until just a bit ago...the tables aren't the problem, I don't think...those were the given items to be worked from, and I can't change those, they were set up by the person who's wanting this done.

Unless it's something about how you linked them, but since they're different (i.e., now have different fields) in your version, I'm not seeing how that can work.

Can you tell me more about why you changed them? I've fiddled with the originals a bit based on what I saw, but I had to put everything back because it changed other things (like, the phases turned into their number instead of their abbreviated name, which won't work, either).

Feeling a bit hopeless, here...

 

[catalin.petrut]

Till a lit later i'm on mobile so i can't review your atached files. But i think that a lot of the fields in excel are calculated fileds. This kind of fields don't need to be inserted into databases.