I was hoping someone could help me with this please.

I used Access 2000 ages ago and can't remember how this is done. I have spent too long looking on the net for how to do this so i thought someone on here could help.

I have a table which i have imported from an excel spreadsheet.
I have designed a simple form for data entry.

I dont know if this will be a query or a report but i need to create a report to mirror the excel filter function.

i.e.

We have a field called suppliers. In this field there are three different suppliers.
I also have a Cost Amount field and Date to run on this report.

I want to be able to select a date and/or date range and filter it further by supplier and have the accumulated cost amount.

Report instance:
I want all orders from Supplier A on a certain date and the cost amount for each record with a total.
I want all orders from Supplier B on a certain date and the cost amount for each record with a total.
I want all orders from Supplier C on a certain date and the cost amount for each record with a total.

Obviously with Excel this can be done using filters but this is no good as the spreadsheet is becoming unstable, hence putting it into Access. Also the users only have Access runtime and only have the form/switchboard.

I hope i explained this properly and would really appreciate any help with this.

Cheers
Steve

as a start - after you imported the data into access, did you normalise the tables? or have you still got just one big ugly table?

as a start - after you imported the data into access, did you normalise the tables? or have you still got just one big ugly table?

Hi,
I formatted the excel sheet properly so access could read the data correctly such as Date fields. I don't understand what you mean by normalise as i am not too familiar with Access however i still have my big ugly table (which is where the form pulls it's fields from) and the contents of this have not changed.
Some of the fields have hyphens but this seems not to matter if that fields format was text. The only thing i have done is rename the fields from what they were imported as and used some, but not all on the form.

Hope this answers your question.

i'm so sorry for the delay in my reply - this thread seems to have dropped off my radar. did you manage to figure out your problem?

perhaps this may help:
http://support.microsoft.com/kb/208548/en-us (http://support.microsoft.com/kb/208548/en-us)

to help you apply a filter, you can create a query to pull unique/distinct suppliers form your table. (i.e., if you have 20 records, but only about 3 suppliers, the query should return only three rows of data: the three different suppliers)

then use that query as the rowsource of a combobox on your form. once you do that, you can use the combo as the source for your filter. the link above will give you a head start on filters.

normalisation: is where you have separate tables for entities in your DB which are themselves separate. (i.e., an employee has nothing to do with a supplier, so although an employee makes an order to be filled by a supplier, they should live in different tables)

i.e., one big ugly table has repeated information about, say, suppliers, which has to be inputted each time a new record is made for an order. NORMALISED tables have one table for suppliers, another table for orders. a simple table for suppliers would look something like:

tblSuppliers
=============
SupplierID (PK, Autonumber)
Supplier (Text)
SupplierWebsite (Text)
SupplierAddress (Text)

and then orders would be:

tblOrders
==========
OrderID (PK, Autonumber)
OrderNum (Text)
QuantityOrdered (Number)
SupplierID (FK, Number)
DateOrdered (Date/Time)

then, whenever you make an order, instead of having to fill out the supplier details each time into a big table, you just have to tell the orders table which supplier you want to use - which is customarily done via a form where the SupplierID textbox is changed into a combo (right-click on textbox and select "Change to...") and the combo's source is a query to pull the Supplier name out (i.e., tblOrders stores the SupplierID autonumber, but this is hard to read for a human on a form, so the combo finds out for the user which supplier name belongs to that supplierID, and displays it).

a warning, though - don't use the lookup wizard at table level (http://www.mvps.org/access/lookupfields.htm), it is evil and will make your life harder. instead, do it like i've just described by making the form have a 'lookup' (by way of combo box).