MS Access Experts

[Mjcpr]

So, I'm taking the initiative to learn Access for work. There won't be any really complicated projects that I will use it for.

That said, who wants to be the person I peem all of my simple questions to? There are some tiny spek points in it for you. :D

And please, not everybody at once.

[JohnnyMack]

Out.

YWIA.

[Fugue]

http://www.smiliearchiv.com/smilies/tiere/tiere_63.gif

[sooner_born_1960]

select * from employees;

YWIA.

[Mjcpr]

Neg * [JohnnyMack] [Fugue] [soonerborn1960]

[Newbomb Turk]

I haven't used Access in 4 or 5 years. To build a simple database, it's pretty easy to use.

[Fugue]

I haven't used Access in 4 or 5 years. To build a simple database, it's pretty easy to use.

one must consider the user :D

[Fugue]

Neg * [JohnnyMack] [Fugue] [soonerborn1960]

your negs would be better spent in the "WTF" recruiting thread. :mad:

[Newbomb Turk]

one must consider the user :D

right.

good luck Pat - you're gonna need it.

[OklahomaTuba]

It's easy as hell.

[Mjcpr]

If I have a query that produces a few rows of data with some fields in those rows containing $ values, can you total those values somewhere? Or do you have to make a report or something?

TIA

[mdklatt]

If I have a query that produces a few rows of data with some fields in those rows containing $ values, can you total those values somewhere? Or do you have to make a report or something?

TIA

You can total the values as part of the query, depending on what you're trying to do.

[Mjcpr]

You can total the values as part of the query, depending on what you're trying to do.

I think this would be more like totaling the totals.

[mdklatt]

n/m

[mdklatt]

I think this would be more like totaling the totals.

You can do that, too. Your query will return a single record with just the totals. If I want the sum of all MONEY fields in a table, it's as easy as

SELECT SUM(MONEY) FROM TABLE

If you want to get more fine-grained results, take a look at the GROUP BY clause.


Note that I'm talking about SQL in general, not Access (which I have only a tiny bit of experience with).

[Mjcpr]

You can do that, too. Your query will return a single record with just the totals.
That's what I have, several records that each contain (among other things) a dollar value. I was just wondering if you could total those in some way other than a report.

I have:

ABDCD 11 $5.00
FDGJK 14 $8.50

I need:

Total 25 $13.50

Or something.

[mdklatt]

That's what I have, several records that each contain (among other things) a dollar value. I was just wondering if you could total those in some way other than a report.

I have:

ABDCD 11 $5.00
FDGJK 14 $8.50

I need:

Total 25 $13.50

Or something.


If the second column is X and the third column is Y it's simply:

SELECT SUM(X), SUM(Y) FROM TABLE;

[Mjcpr]

If the second column is X and the third column is Y it's simply:

SELECT SUM(X), SUM(Y) FROM TABLE;

If that's even doable in Access, it's well beyond my level of understanding. :O

[mdklatt]

If that's even doable in Access, it's well beyond my level of understanding. :O

Then stay away from the GROUP BY clause for awhile.

[Tulsa_Fireman]

Speaking of Access...

I've got an issue I've been lookin' at myself to solve a departmental issue. Personnel tracking. Maybe you DB fellers can clarify for me as to the depth of the task.

700 some odd personnel. Roughly 20 different classifications of duty (on duty, vacation, sick, administrative leave, sick 1/2 shift, et cetera). And the ability, with a simple front end, to enter either per person or per assignment what they are for the day. That, and the ability to drag reports for X amount of time to say 6 months ago, on X day, Joe Bob Fireman worked on Engine 25 for the day, then took a half shift of vacation. Or Joe Bob transfered for the shift to fill a vacancy on Engine 20. Or he got explosive diarrhea and had to bail.

Is Access even feasible for something of this scope?

[Getem]

Mjcpr, I don't have Access installed right now at home, but what I think you do is create a new query based on the table of interest, then pull down the fields you want to sum to the lower part of the query designer. Under the View menu (I think or maybe the Query menu) there should be "View Totals". Select that, and down where you have dropped those fields, there should be a line called Total. Under each field you want to sum, drop the combo box down on the total line and select Sum. Then run the query. Hope that works. Or, once you create the query, under the Query menu somewhere, there is an option called SQL View, which you could then copy and paste mdklatt's answer to.

[Getem]

Speaking of Access...

I've got an issue I've been lookin' at myself to solve a departmental issue. Personnel tracking. Maybe you DB fellers can clarify for me as to the depth of the task.

700 some odd personnel. Roughly 20 different classifications of duty (on duty, vacation, sick, administrative leave, sick 1/2 shift, et cetera). And the ability, with a simple front end, to enter either per person or per assignment what they are for the day. That, and the ability to drag reports for X amount of time to say 6 months ago, on X day, Joe Bob Fireman worked on Engine 25 for the day, then took a half shift of vacation. Or Joe Bob transfered for the shift to fill a vacancy on Engine 20. Or he got explosive diarrhea and had to bail.

Is Access even feasible for something of this scope?

Access would work well for that, but do backups often. It's not as reliable as the corporate level databases. Mainly it's when you have multiple users hitting the database at one time that Access has performance/reliability issues. But the reports, forms and automation are all very easy to work with.

[Mjcpr]

Why would the sort order of dates on a report be out of whack when the query the report is based on is sorting correctly?

Query: 4/9 4/10 4/11 4/12 4/13
Report: 4/10 4/11 4/12 4/13 4/9

[Howzit]

http://www.daviddarling.info/images/abacus.jpg

[mdklatt]

Why would the sort order of dates on a report be out of whack when the query the report is based on is sorting correctly?

Query: 4/9 4/10 4/11 4/12 4/13
Report: 4/10 4/11 4/12 4/13 4/9

It looks like the reporting tool thinks those are strings and not dates, which is why "4/9" is last. Can you get the dates in a MM/DD format, or tell the reporting tool that those are dates?

[Mjcpr]

The field is defined as a date field in the table, so shouldn't that be all you need to do? On the report control property there is no drop down menu under formats, although you can type it in if you know it, which I did. This report has worked fine for weeks, until today.

[mdklatt]

The field is defined as a date field in the table, so shouldn't that be all you need to do? On the report control property there is no drop down menu under formats, although you can type it in if you know it, which I did. This report has worked fine for weeks, until today.

I really don't know any Access specifics. I was just speculating based on my experience with dates in Excel.

[Mjcpr]

I really don't know any Access specifics. I was just speculating based on my experience with dates in Excel.

Then what am I paying you for?

[mdklatt]

Then what am I paying you for?

Your check hasn't even cleared yet, beyotch.

[Mjcpr]

I think you're right about the STRING/DATE value thing, though....I just don't know how to fix it in the report.

[Howzit]

I really don't know any Access specifics. I was just speculating based on my experience with dates in Excel.

Mjcpr's experience with dates is pretty much based on speculation too.

[BlondeSoonerGirl]

I think you're right about the STRING/DATE value thing, though....I just don't know how to fix it in the report.

Rub some dirt on it.

[Mjcpr]

Rub some dirt on it.

Et tu, Boote'?

Since you're the resident db expert, I'ma start peeming all my questions to you in about a minute if'n I don't get some answers.

[BlondeSoonerGirl]

Sometimes, the query pulls the data but the sort/display options change it. It is weird that it worked up until today, though. You sure nothing changed?

Can you see any of those sort/display settings?

[Howzit]

Sometimes, the query pulls the data but the sort/display options change it. It is weird that it worked up until today, though. You sure nothing changed?

Can you see any of those sort/display settings?

Yeah, that's just what I was about to axe.

[Mjcpr]

Can you see any of those sort/display settings?

In the query? Yeah, I can see them.....the query is sorted correctly, it's the report where the problem occurs.

[BlondeSoonerGirl]

No...it the report itself...

Think along the same lines as Excel...when you sort data you can fine tune it to show the date in a specific format. It still sorts by the date but it displays it differently.

Whoo-boy, I'm on a roll now...

[mdklatt]

Think along the same lines as Excel...when you sort data you can fine tune it to show the date in a specific format. It still sorts by the date but it displays it differently.



:les: THAT'S WHAT I SAID!

[BlondeSoonerGirl]

:les: THAT'S WHAT I SAID!

I know but I think he's got you on ignore.

:eddie: