Query help

[Mikey-]

It's been some time since I've used Access. I use to teach it a long time ago and boy has it waned. Having issues getting results. This is a purchasing transaction file that was imported from .xls. The import contains the date of the transaction, amount, names and addies, and store number.

I'm having issues with some queries. Does anyone have any ideas on how to do the following:

Individuals who visit multiple stores (store number) and purchases >=$3000 in one day.
Individuals who visit multiple stores (store number) and purchases >=$3000 in one week.

I have others but they are variations of this. I've attached the file for convenience.

Thanks folks,
Mike.

[NoellaG]

Hi,

I created to queries as examples in your database. take care that this only works if the combination of last + first name is unique. In fact in Access you would need to create a table with customers, one with the stores and then the table with the sales, all combined through 2 one-to-many relationships.

hop this already helps.

grNG

[weekend00]

Sorry, I can not download .mdb file because of company policy.
would you attach a .zip file?

[NoellaG]

Hi,

the attached file IS a zip file ???????????????????????

grNG

[weekend00]

I was looking at Mikey's post.

[Mikey-]

Hi,

I created to queries as examples in your database. take care that this only works if the combination of last + first name is unique. In fact in Access you would need to create a table with customers, one with the stores and then the table with the sales, all combined through 2 one-to-many relationships.

hop this already helps.

grNG

Thanks Noella.

I'll look at this. Thanks. In the meantime I was thinking exactly what you said (creating multiple tables with relationships), however, this comes to me once a week as an Excel file and gets imported to a single access table. I'd have to breakout the one table into at least 3 tables. Each week would obviously be different and need the same treatment all over again.

Not sure how to handle this.

[Mikey-]

Hi,

I created to queries as examples in your database. take care that this only works if the combination of last + first name is unique. In fact in Access you would need to create a table with customers, one with the stores and then the table with the sales, all combined through 2 one-to-many relationships.

hop this already helps.

grNG

Ok, here's the issue. In my haste to get a result I didn't explain fully.

Let me expand on my issue.

Your query works well. Now add the fact that I need to see the individual store numbers and then get a total amount for each individual over the their multiple store visit.

Now it's getting complicated, huh.

[NoellaG]

Hi,

I included a second example, but remember you can also create reports with subtotals and totals.

grNG