Help needed with form for sales analysis

[kennyharrill]

We are trying to develop a tool for us to be able to analyze the sales by quantity and dollar amount of our heat transfers and designs. The columns in our table are PARTCODE, SALEMONTH, SALEYEAR, SALEQTY, SALESALES, and URL. A typical row looks like this:

4173L, 1, 2005, 44, $550.00,

http://www.mylink.com/images/lg/4311.jpg

(The one in the second column stands for January)

We are hoping to utliize an Access form (I can email a copy of a screenshot showing what we're hoping to accomplish to anyone who may be interested). We are wanting each page to display one image. There are 18 different controls that we would like showing up:

1. The total number of the transfer sold, by quantity, for year up to date.
2. Breakdowns of number sold by month (12 total) for the current year.
3. Totals sold by dollar amount for the current year and the previous four.

I've been able to get this to work - somewhat. The controls work fine; however, when I display the form in Form view, the navigation is showing all 423 records. In our sample table, we're using only two design numbers - thus, we're trying to get our form to show only two records. Eventually, we want to be able to gather information from a SQL Server database.

I sure would appreciate any help that anyone can give us. This has got us totally stumped. I was told in another forum to try adding subforms to the page. That's ridiculous because all it does is make the page crowded. We don't want one form with 18 crowding subforms. We want to be able to display just the actual numbers, actual results, and we hope that surely someone has developed something similar to this or can help us.

Again, if you would like for me to email a copy of the diagram, please let me know. Many thanks

Ken

 

[ZmrAbdulla]

Unfortunately the link doesn't work...

As far as I understand you need to display some data from the table or query on some textboxes right?

Try to use DLookUp() or some other Domain aggregate functions. It is something like

[tt]
=DlookUp("[FieldName]","TableName")[/tt]

you can even use criteria. see more here.

http://www.mvps.org/access/general/gen0018.htm

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[kennyharrill]

I just typed a sample link for illustration purposes. Another forum suggested =DSum, which those formulas actually work, but they're not summarizing just two things - the navigation is showing all 400+ rows in the table. I'll give this a try. We don't want to navigate through all the rows in our form - we want a form, report, or data page that will summarize. We can do breakdowns in our table, but we can't get it to work on our form.

 

[kennyharrill]

Tried inserting a text box for our part code field using =DLookup("[PARTCODE]", "tblSalesAnalysis"). Didn't work - navigation still goes through all 400+ rows. Our sample table's PARTCODE column utilizes just two design numbers; eventually, we'll need to tie this form to our SQL server database in development (we have well over 5,000+ designs and we would like to be able to utilize this form for a single design or a group of them - category if you will).

 

[ZmrAbdulla]

DSum() is for calculating a field from a set of records.
DLookUp() is for finding a fieldvalue from a recordset.
DCount() to count number of records.
DFirst()..........
DLast()...........
Like these Domain aggergate function go..

Can you email the screen shot to me? you will get my email id from my site. Your image link is not working.If you can give a working link then that will be fine. So that others too can see the problem.

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal