New to Access - Query

[coops2k]

Hi all,



I haven't used access in a very long time but i need to do this if you can help and i am not sure on the best method or if its possible.

I have the following table:

ID*	Name	No
1	Jim	A1111
2		A1132
3		B3324
4		A1123
5	Harold	B1234
6		A4432
7	Jill	A1243

I have around 8000 rows of data in a table and I need to just display the Name and 1st A and B that show after the name.....
Jim A1111 B3324
Harold B1234 A4432
Jill A1243

Any ideas on the best solution for this, I have managed to do a query and just display the Name when its <>"" so it shows the first entry but I don't know how to display the next part?

Thanks

Paul

[coops2k]

If it helps i can now get the name to appear in every row so is it possible to just display the first A and B numbers found against every name?

Thanks
Paul

[Micron]

You would greatly simplify this if you fill in the missing values, otherwise there's nothing to suggest that a value belongs to any particular name. Once that's done, this should be doable with a Totals query if you group by name, and return the No for the Min (in one field) and Max (in another field) ID for each group.

[JoeM]

As Micron said, it is best to populate the name for each record.
One easy way to do this is temporary take the data out to Excel, and populate it there.
You can do it really quick and easy there using this method: https://www.extendoffice.com/documen...lue-above.html
Once, you do that, just re-import it back to Access, and then you can use the method Micron suggests.

[coops2k]

All the rows now have filled data for the name tag, however when i do a query i get mixed results. I query using group by NAME and a Column with No Total Min Like A* and another Column with No Total Min Like B*.


Is that correct?

[Micron]

No, but post your query sql when you have such questions as it usually helps us quite a bit.
Or try changing according to my first post, which is to get Max of ID and Min of ID, not A or B

[coops2k]

Heres some of the actual data (changed the names to make it easier before):

Rec	Number
1	E1000
1	E1001
1	E1002
1	E1003
1	E1004
1	E1005
1	E1006
1	E1007
1	E1008
1	E1009
1	E1010
1	E1011
1	E1012
1	E1013
1	E1014
1	E1015
1	E1016
1	B1000
2	B1001
2	B1002
2	B1003

The Bxxxx and Exxxx can appear anywhere in a Rec but will be in number order. I have 450 records with 7000 rows of data, each record can contain different amounts of data from 1 row up to 30 rows.

When i use the Max query against an E it works great and is correct but when i use the Min to get the first E number of each record it goes complety wrong.

Max Query:
SELECT ImportData2.Rec, Max(ImportData2.Number) AS MaxOfNumber
FROM ImportData2
GROUP BY ImportData2.Rec
HAVING (((Max(ImportData2.Number)) Like "*E*"))
ORDER BY ImportData2.Rec;

Min Query:
SELECT ImportData2.Rec, Min(ImportData2.Number) AS MinOfNumber
FROM ImportData2
GROUP BY ImportData2.Rec
HAVING (((Min(ImportData2.Number)) Like "*E*"))
ORDER BY ImportData2.Rec;

DB Setup
Rec is setup as a Number
Number is setup as a ShortText - this might be my issue but i need to keep the E and B next to the value, unless it would be easier to break this out in excel first.

[Micron]

You're confusing me. What happened to ID field from the first data? You have completely changed things if you have eliminated it as now there's really no order for "number" field.
Not sure this is relevant now, but you've created 2 queries when only one was suggested.

If you have no ID field as you first showed, then this is a whole different ball game. You'd also have to provide new expected results that are relevant to you new 'data' example, such as
1 E1000
1 E1016

If it should be
1 E1000
1 B1000
then I'm afraid you're out of luck. If this is how you created your tables and data, that is the root of your problem. If this is imposed on you from some other application, then I don't know what you can do aside from attempting to get some semblance of order from that data because it seems at this point that there is none.
EDIT - to comment on your notion of breaking out E from 1000: always a good idea in Access data AFAIC. However, my point is that without imposition of a definitive order, numeric values as text (e.g. 1000) are a non-starter since 1000 comes before 200 in a text field.

[coops2k]

OK i have the query working by seperating the E and B from the values in excel and reimporting the data back in.

Can i link the data from the query to a form and link data against record number?

[Micron]

If you're asking if you can make a form recordsource property the name of your query, then yes - but not queries. I don't know what the second part of that question means.
Did you see my caution about numbers as text? Sometimes results seem valid at first, then along comes data or a situation that proves to be nonconforming.

[coops2k]

Thanks Micron. I seperated the text from the numbers in the end to make it easier.

Think im sorted now, have two querys one for B and one for E to find the min value in each record. Created relationship against Rec in Table to both Query Rec and have now done a form to show the results.

Thanks for the help.

[Micron]

You're welcome. Please mark you thread as solved if you're done with it.
Thanks.